Excel: Activate worksheet with similar names 2

[Sareimer]

Can anyone tell me how to activate the first worksheet that meets Inputbox criteria. For example, in a workbook of 50 sheets, I have a five worksheets that beging with the letters "ST" Even though I may have 5 worksheets that being with "ST", I want VB to activate the first "ST" worksheet.

Any help will be appreciated.

Steph

 

[SkipVought]

Hi,

dim ws as worksheet
for each ws in worksheets
  with ws
    if left(.name, 2) = "ST" then
      .activate
      exit for
    end if
  end with
next

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Tranman]

Steph,
I think something like this will do, or at least get you on the right track:

Private Sub tester()
Dim sht As Worksheet
Dim strInput As String
strInput = InputBox("Please enter sheet name.", "GET SHEET NAME&quot

For Each sht In Sheets
If UCase(Left(sht.Name, 2)) = UCase(strInput) Then
sht.Activate
Exit For
End If
Next
MsgBox "No sheets matched your input string", vbOKOnly
End Sub

In this example, you are looking at a 2-character input string. If you plug it into a default Excel workbook, and enter sh, it will activate sheet1 every time, but you get the idea. To the best of my recollection, the sheets collection is exposed from left to right in the for loop, so the first one that matches your criterion should be activated.

Let me know if this doesn't do the trick.

Tranman

 

[Sareimer]

Those both sound good. I am trying to plug this into a larger code:


Option Explicit

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

****************
I'm assuming I would have to put the new code into the true statement?

 

[SkipVought]

I'd make a Function

Function FirstSheetName(sVal as String)
dim ws as worksheet
for each ws in worksheets
  with ws
    if left(.name, 2) = sVal then
      FirstSheetName = ws.Name
      exit for
    end if
  end with
next
FirstSheetName = "NO NAME"
End Function

Then call it

sSheetName = FirstSheetName("ST")
If sSheetName <> &quot;NO NAME&quot; Then 
  Sheets(sSheetName).Activate
else
  'what to do if no sheet
end if

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Loomah]

Steph
I'd just like to throw in another little option. Like your own code this will allow users to enter more than 2 letters and uses the Like method to match sheets.

Sub mit()
Dim sSheet As String
    sSheet = SheetSelection(InputBox("enter sheet name"))
    If Len(sSheet) > 0 Then
        Worksheets(sSheet).Activate
    End If
End Sub

Function SheetSelection(Optional sh As String) As String
Dim ws As Worksheet
    For Each ws In Worksheets
        If UCase(ws.Name) Like UCase(sh) & "*" Then
            SheetSelection = ws.Name
            Exit For
        End If
    Next
End Function

End result's the same it's just another option!
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244