Finding a worksheet

[Patrick C. Simonds]

I have a workbook which ranges from 40 to 55 worksheets, I thought it would
be helpful if I were to create a UserForm which would list the name of all
available worksheets (by be as a series of OptionButtons). The user would
then select the sheet they wanted to view next from the list and that sheet
would become active. Has anyone seen or done anything like this?

 

[Tim Williams]

It might be easier to use a listbox rather than option buttons.
Or train all your users to instead right-click on the sheet navigation
arrows at bottom-left....

Tim

 

[johnmanivong]

It might be easier to use a listbox rather than option buttons.
Or train all your users to instead right-click on the sheet navigation
arrows at bottom-left....

Tim





- Show quoted text -

have a userform, a combobox and a button and try this
Private Sub CommandButton1_Click()
Dim i As Long
Dim j As Long
For i = 1 To Worksheets.Count
j = i
Me.ComboBox1.AddItem Worksheets(j).Name
j = j + 1
Next i
End Sub

Private Sub ComboBox1_Change()
Dim kon As String
kon = Me.ComboBox1.Value
Sheets(kon).Select
End Sub

 

[Gary Keramidas]

i've done it, but with a lot less sheets:

design a form with the optionbuttons (1 thru 8 in my case) named
optionbutton1, optionbutton2.....)

then when the form loads i just use this to populate the form with the sheet
names:

For i = 1 To 8
Me.Controls("optionbutton" & i).Caption = Worksheets(i).Name
Me.Controls("optionbutton" & i) = False
Next

then the code for each optionbutton can be something like this:

Private Sub OptionButton1_Click()
Worksheets(1).Activate
End Sub

 

[Patrick C. Simonds]

Forget training them, apparently I need training. I did not know that one.
Does that also work in Office2003?

 

[john]

there is an easier way. Place this code in Thisworkbook Excel Objects sheet:

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target
As Range, Cancel As Boolean)
On Error Resume Next
If ActiveWorkbook.Sheets.Count <= 16 Then
application.CommandBars("Workbook Tabs").ShowPopup 500, 225
Else
application.CommandBars("Workbook Tabs").Controls("More
Sheets...").Execute
End If
On Error GoTo 0
Cancel = True
End Sub

Instruct your users to right click anywhere on worksheet a PopUp menu will
appear with list of all sheet names.

Hope useful

 

[Dave Peterson]

You may want to try this from Debra Dalgleish's site:
http://contextures.com/xlToolbar01.html

 

[Gord Dibben]

You can use the right-click on the navigation arrows or build a Navigation
Toolbar as per Dave's code on Debra Dalgleish's site.

http://www.contextures.on.ca/xlToolbar01.html

Or see this google search thread for the BrowseSheets macro from Bob Phillips.

http://tinyurl.com/yoa3dw

I'm kinda partial to Bob's method.


Gord Dibben MS Excel MVP