Saturday, 18 June 2016

Excel Find a sheet based on name

This is not a question, so much as a solution, but I wanted to share it here as I had gotten help for things I needed here.




I wanted to find a specific Excel sheet, in the Active Workbook, searching by the name of the sheet. I built this to find it. It is a "contains" search, and will automatically go to the sheet if it is found, or ask the user if there are multiple matches:



To end at any time, just enter a blank in the input box.




Public Sub Find_Tab_Search()
Dim sSearch As String
sSearch = ""
sSearch = InputBox("Enter Search", "Find Tab")
If Trim(sSearch) = "" Then Exit Sub

'MsgBox (sSearch)

Dim sSheets() As String
Dim sMatchMessage As String
Dim iWorksheets As Integer
Dim iCounter As Integer
Dim iMatches As Integer
Dim iMatch As Integer
Dim sGet As String
Dim sPrompt As String


iMatch = -1
iMatches = 0
sMatchMessage = ""

iWorksheets = Application.ActiveWorkbook.Sheets.Count
ReDim sSheets(iWorksheets)

'Put list of names in array
For iCounter = 1 To iWorksheets

sSheets(iCounter) = Application.ActiveWorkbook.Sheets(iCounter).Name
If InStr(1, sSheets(iCounter), sSearch, vbTextCompare) > 0 Then
iMatches = iMatches + 1
If iMatch = -1 Then iMatch = iCounter
sMatchMessage = sMatchMessage + CStr(iCounter) + ": " + sSheets(iCounter) + vbCrLf
End If
Next iCounter

Select Case iMatches
Case 0

'No Matches
MsgBox "No Match Found for " + sSearch
Case 1
'1 match activate the sheet
Application.ActiveWorkbook.Sheets(iMatch).Activate
Case Else
'More than 1 match. Ask them which sheet to go to
sGet = -1
sPrompt = "More than one match found. Please enter number from following list"
sPrompt = sPrompt + "to display the sheet" + vbCrLf + vbCrLf + sMatchMessage

sPrompt = sPrompt + vbCrLf + vbCrLf + "Enter blank to cancel"
sGet = InputBox(sPrompt, "Please select one")
If Trim(sGet) = "" Then Exit Sub
sPrompt = "Value must be a number" + vbCrLf + vbCrLf + sPrompt
Do While IsNumeric(sGet) = False
sGet = InputBox(sPrompt, "Please select one")
If Trim(sGet) = "" Then Exit Sub
Loop
iMatch = CInt(sGet)
Application.ActiveWorkbook.Sheets(iMatch).Activate

End Select

End Sub


I hope someone finds this useful, and would also welcome enhancement suggestions.

No comments:

Post a Comment

c++ - Does curly brackets matter for empty constructor?

Those brackets declare an empty, inline constructor. In that case, with them, the constructor does exist, it merely does nothing more than t...