Create a worksheet named Index and move it to the leftmost position.

Then drop a command button from the Forms toolbar onto that Index worksheet.

Assign it this macro:

Option Explicit
Sub auto_open()

Dim wCtr As Long
Dim HowMany As Long

Application.ScreenUpdating = False

Worksheets("index").Visible = xlSheetVisible

For wCtr = 1 To Worksheets.Count
If Worksheets(wCtr).Name = Worksheets("index").Name Then
'skip it
Else
Worksheets(wCtr).Visible = xlSheetHidden
End If
Next wCtr

HowMany = CLng(Application.InputBox(Prompt:="how many to show?", Type:=1))

If HowMany < 1 Then
'do nothing
Else
If HowMany Worksheets.Count - 1 Then
HowMany = Worksheets.Count - 1
End If

For wCtr = 2 To HowMany + 1
Worksheets(wCtr).Visible = xlSheetVisible
Next wCtr
End If

Application.ScreenUpdating = True

End Sub

By naming it auto_open, it actually runs when the workbook opens. And hides all
the other worksheets.

But it still can be run via the button.


David wrote:

I have a worksheet that has data for up to 50 technicians. Each location gets
this workbook and may have from 3-50 technicians. I'd like to create a
macro/formula that lets each location enter the number of technicians, and
then will hide the unneeded worksheets.
The workbook has a summary page, (worksheet one), fifty individual
technician pages (worksheets 2-51) and a setup page (worksheet 52). Data is
entered on each technicians page.
What I would like is for the location to entry a number in a cell or from a
macro prompt that asks how many locations they have. Based on that response,
say 10, would leave the first 10 technician pages, but hide the other 40.
Any suggestions? Thanks!

--

Dave Peterson