Excel: Finding Worksheet in Wrkbk using code (Update) 2

[Sareimer]

Back in Nov there was a code posted for finding and activating a worksheet in Excel. thread707-697281

My question is, can variables be used to find the closest match for a worksheet name. In other words, I don't want users to have to type in the exact name of the worksheet but, instead the first several characters.

Thanks,
Steph

 

[PHV]

You may consider to browse your workbook Sheets collection and do some InStr or comparisons with Left$.

Hope This Help
PH.

 

[Zathras]

Here is some code to implement PHV's suggestion:
To test it, I set up a workbook with four sheets: Lions, Tiggers, Tigers and Bears (Oh my!).
[blue]

Option Explicit

Sub test()
Dim FindSheetName As String
Dim HowManyFound As Integer
  FindSheetName = "tig"
  If UniqueSheetName(FindSheetName, HowManyFound) Then
    MsgBox "SheetName is " + FindSheetName
  Else
    MsgBox HowManyFound & " sheets found like " & FindSheetName & "..."
  End If
End Sub

Function UniqueSheetName(ASheetName As String, HowMany As Integer) As Boolean

[green]

' Provide a partial sheet name in the ASheetName argument.
' If a unique match is found, function returns true, ASheetName
'    is updated with the actual sheet name that was found
'    and HowMany is set to 1.
' Otherwise, function returns false and HowMany indicates
'    how many sheets made the partial match (0, 2, 3, or whatever).

[/color]

Dim sht As Worksheet
Dim sUCaseSheetName As String
Dim nLength As Integer
Dim TheSheetName As String
  HowMany = 0
  sUCaseSheetName = UCase(ASheetName)
  nLength = Len(ASheetName)
  For Each sht In Application.Worksheets
    If UCase(Left(sht.Name, nLength)) = sUCaseSheetName Then
      TheSheetName = sht.Name
      HowMany = HowMany + 1
    End If
  Next
  If HowMany = 1 Then
    ASheetName = TheSheetName
    UniqueSheetName = True
  End If
End Function

[/color]

 

[Sareimer]

Thank you both!!

The code works well to idenify the name of the worksheet. I was able to add in the [InputBox]. Any ideas on how to then activate the desired worksheet?

Steph

 

[PHV]

The answer is in the thread you mentionned Thread707-697281

 

[Sareimer]

GOT IT!!

Here is the full Code:


Sub test()
Dim FindSheetName As String
Dim HowManyFound As Integer
FindSheetName = InputBox("Enter Customer Name", "Worksheet Selection&quot
If UniqueSheetName(FindSheetName, HowManyFound) Then
MsgBox "SheetName is " + FindSheetName
Sheets(FindSheetName).Activate
Else
MsgBox HowManyFound & " sheets found like " & FindSheetName & "..."
End If
End Sub

Function UniqueSheetName(ASheetName As String, HowMany As Integer) As Boolean
' Provide a partial sheet name in the ASheetName argument.
' If a unique match is found, function returns true, ASheetName
' is updated with the actual sheet name that was found
' and HowMany is set to 1.
' Otherwise, function returns false and HowMany indicates
' how many sheets made the partial match (0, 2, 3, or whatever).
Dim sht As Worksheet
Dim sUCaseSheetName As String
Dim nLength As Integer
Dim TheSheetName As String
HowMany = 0
sUCaseSheetName = UCase(ASheetName)
nLength = Len(ASheetName)
For Each sht In Application.Worksheets
If UCase(Left(sht.Name, nLength)) = sUCaseSheetName Then
TheSheetName = sht.Name
HowMany = HowMany + 1
End If
Next
If HowMany = 1 Then
ASheetName = TheSheetName
UniqueSheetName = True
End If
End Function