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.

RB Smissaert wrote:
Make it:

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

To avoid a possible error.

RBS

"RB Smissaert" wrote in message
...
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


"excelnut1954" wrote in message
oups.com...
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.