Finding a worksheet
 

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?

Finding a worksheet
 

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

"Patrick C. Simonds" wrote in message
...
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?

Finding a worksheet
 

On Feb 23, 9:50*pm, "Tim Williams" <timjwilliams at gmail dot com
wrote:
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

"Patrick C. Simonds" wrote in . ..



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?- Hide quoted text -

- 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

Finding a worksheet
 

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

Gary


"Patrick C. Simonds" wrote in message
...
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?

Finding a worksheet
 

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


"Tim Williams" <timjwilliams at gmail dot com wrote in message
...
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

"Patrick C. Simonds" wrote in message
...
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?

Finding a worksheet
 

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
--
JB


"Patrick C. Simonds" wrote:

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?

Finding a worksheet
 

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


"Patrick C. Simonds" wrote:

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?

--

Dave Peterson

Finding a worksheet
 

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

On Sat, 23 Feb 2008 21:20:11 -0800, "Patrick C. Simonds"
wrote:

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?