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

unless the file is an addin at least one sheet must be visible.

You could try formatting an opening sheet without gridlines and with a color
or pattern to make it not look like a sheet....

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

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