macro to show list of worksheets in a workbook

[excelnut1954]

I'm writing macros to perform searches. There are multiple sheets the
desired record can be found, based on dates of activity, etc. So, there
will be sheets that will have different names as time goes on.

What I want is to be able to show the user all the sheets available,
then he can click which one he wants to search.

I've written code to perform searches based on picking sheets, but in
those cases, the sheet names will be static. In this new case, the
sheet names will vary over time. That's why I want the list of all the
sheet names to come up.

So, it is possible to: 1) show the user all the sheet names, and 2)
from that list, let him click which one he wants to search in?

Thanks
J.O.

 

[Ron de Bruin]

Hi excelnut1954

You can use a Userform with a listbox and add this code in the
userform module.
Note : this code not check for hidden sheets (list all sheets)

Private Sub ListBox1_Change()
Sheets(ListBox1.Value).Select
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim wks As Worksheet
Dim Wb As Workbook
Set Wb = ThisWorkbook
For Each wks In Wb.Worksheets
UserForm1.ListBox1.AddItem wks.Name
Next
UserForm1.ListBox1.SetFocus
End Sub

If you need more help post back

 

[RB Smissaert]

This is a simple way to do it without a UserForm:

Sub SheetPicker()

Dim i As Long
Dim sh As Worksheet
Dim collSheets As Collection
Dim strSheets As String
Dim lSheetIndex As Long

Set collSheets = New Collection

For Each sh In ThisWorkbook.Sheets
collSheets.Add sh.Name
Next

strSheets = "1. " & collSheets(1)

If collSheets.Count > 1 Then
For i = 2 To collSheets.Count
strSheets = strSheets & vbCrLf & i & ". " & collSheets(i)
Next
End If

lSheetIndex = Application.InputBox("Pick the number to pick a sheet" & _
vbCrLf & vbCrLf & _
strSheets, "", 1, , , , , 5)

If lSheetIndex > 0 Then
MsgBox collSheets(lSheetIndex), , "picked sheet"
End If

End Sub


RBS

 

[RB Smissaert]

Make it:

If lSheetIndex > 0 And lSheetIndex <= collSheets.Count Then
MsgBox collSheets(lSheetIndex), , "choosen sheet"
End If

To avoid a possible error.

RBS

 

[excelnut1954]

Excellent!! This will work just great. Thanks for the quick, and
helpful response.
Thanks,
J.O.

 

[excelnut1954]

I tried Ron's approach yesterday after he posted his reply. It works
fine.

RBS, I will look at your's, too.

I just started this particular project, so I'll have time to figure out
the approach from RBS, and decide which one will work best for these
users.

Thanks to both of you for your suggestions.

J.O.