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

--
Regards Ron De Bruin
http://www.rondebruin.nl



"excelnut1954" <[email protected]> wrote in message news:[email protected]...
> 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.
>

[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


"excelnut1954" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

[excelnut1954]

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

Ron de Bruin wrote:
> 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
>
> --
> Regards Ron De Bruin
> http://www.rondebruin.nl
>
>
>
> "excelnut1954" <[email protected]> wrote in message news:[email protected]...
> > 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.
> >

[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

"RB Smissaert" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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.
>>
>

[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.

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" <[email protected]> wrote in message
> news:%[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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.
> >>
> >