ExcelBanter - Dialog Boxes

How about...

Create a new worksheet. Call it "Index"

Plop a combobox from the control toolbox toolbar on that worksheet.
(maybe add some instructions to the users on what to do.)

Then behind the ThisWorkbook module, put this code:

Option Explicit
Private Sub Workbook_Open()

Dim iCtr As Long

With Me.Worksheets("Index").ComboBox1
.Clear
For iCtr = 1 To Me.Sheets.Count
If LCase(Me.Sheets(iCtr).Name) = "index" Then
'do nothing
Else
Me.Sheets(iCtr).Visible = xlSheetHidden
.AddItem Me.Sheets(iCtr).Name
End If
Next iCtr
End With

End Sub

Then behind the Index module, put this code:

Option Explicit
Private Sub ComboBox1_Change()

Dim iCtr As Long
Dim mySheetName As String
Dim otherSheetName As String

If Me.ComboBox1.ListIndex < 0 Then
Exit Sub
End If

mySheetName = LCase(Me.ComboBox1.Value)

For iCtr = 1 To Me.Parent.Sheets.Count
Select Case LCase(Me.Parent.Sheets(iCtr).Name)
Case Is = "index"
'do nothing to this sheet
Case Is = mySheetName
Me.Parent.Sheets(mySheetName).Visible = xlSheetVisible
Me.Parent.Sheets(mySheetName).Select
Case Else
Me.Parent.Sheets(iCtr).Visible = xlSheetHidden
End Select
Next iCtr

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

naiveprogrammer wrote:

How can I hide the worksheets in a workbook and only show the dialog box? I
want my users to be able to click and go to whatever sheet they need to.

Thanks!!!

--

Dave Peterson