Menu to view available worksheets

[Guest]

Hi All,
I would like to have a macro to assign to an icon. It would display a menu
that shows the names of all available worksheets in a workbook. All sheets
are hidden except the main worksheet. Some of the hidden worksheets have code
which require a password to access/view. I would like the user to be able to
select any one of the available worksheets but only one at any one time and
if the selected sheet is password protected and they supply an incorrect
password it returns them to the main worksheet.
If any one can help i would be most grateful.
Thank You.

 

[Mike Fogleman]

Here is a start.
In a regular module:

Option Explicit

Sub SheetMenu()
UserForm1.Show
End Sub

You need a UserForm1 and a ListBox1 on that form.
In the userform code module:

Option Explicit

Private Sub UserForm_Initialize()
Dim ws As Worksheet

For Each ws In Worksheets
If ws.Name = "Main" Then
'do nothing
Else
Me.ListBox1.AddItem ws.Name
End If
Next
End Sub

Private Sub ListBox1_Click()
Worksheets(ListBox1.Text).Visible = True
End Sub

This will show the selected sheet, but it is not the ActiveSheet yet. From
here I need to know more about

Some of the hidden worksheets have code
which require a password to access/view.

Some more detail and the code on how this is accomplished, will help me tie
in the above code to the Worksheet code as it is activated.

Mike F

 

[Guest]

Mike,
Thanks for your reply. Here is the code I used to password protect the
sheet. I just click on the sheet tab, view code and paste it in. I hope this
helps.
Regards
--
TonyB
'CODE obtained from MS Community EXCEL Programmers
'Credit & Thanks to: Paul B
Private Sub Worksheet_Activate()
Dim strPassword As String
On Error Resume Next
Me.Protect Password:="MANAGER"
Me.Columns.Hidden = True

strPassword = InputBox("Enter password to access DATA sheet")

If strPassword = "" Then
Me.Previous.Select
Exit Sub
ElseIf strPassword <> "MANAGER" Then
MsgBox "Password Incorrect "
Me.Previous.Select
Exit Sub
Else
Me.Unprotect Password:="MANAGER"
Me.Columns.Hidden = False
End If

On Error GoTo 0
End Sub


Private Sub Worksheet_Deactivate()
On Error Resume Next
Me.Columns.Hidden = True

 

[Mike Fogleman]

OK, modify the code I gave you so far as this,
In a regular module:
Option Explicit

Sub SheetMenu()
UserForm1.Show
Unload UserForm1
End Sub

In the userform code module:
Option Explicit

Private Sub UserForm_Initialize()
Dim ws As Worksheet

For Each ws In Worksheets
If ws.Name = "Main" Then
'do nothing
Else
Me.ListBox1.AddItem ws.Name
End If
Next
End Sub

Private Sub ListBox1_Click()
Worksheets(ListBox1.Text).Visible = True
UserForm1.Hide
Worksheets(ListBox1.Text).Activate

End Sub

And change your worksheets code from Me.Previous to Worksheets("Main"). This
is where you test the password so it should be here you send them back to
the Main sheet if password fails. This will trigger the DeActivate Event and
hide the columns. You may want to go so far as to re-hide the worksheet if
password fails. I added those lines and remarked them.

Private Sub Worksheet_Activate()
Dim strPassword As String
On Error Resume Next
Me.Protect Password:="MANAGER"
Me.Columns.Hidden = True

strPassword = InputBox("Enter password to access DATA sheet")

If strPassword = "" Then
'ActiveSheet.Visible = False
Worksheets("Main").Select
Exit Sub
ElseIf strPassword <> "MANAGER" Then
MsgBox "Password Incorrect "
'ActiveSheet.Visible = False
Worksheets("Main").Select
Exit Sub
Else
Me.Unprotect Password:="MANAGER"
Me.Columns.Hidden = False
End If

On Error GoTo 0
End Sub

Private Sub Worksheet_Deactivate()
On Error Resume Next
Me.Columns.Hidden = True
On Error GoTo 0
End Sub


Mike F

 

[Guest]

Hello Mike,
Thanks for the code. It works like a charm. The only thing I had to do was
move my "Main" sheet to the end after all the other sheets. Otherwise, it
would ask me for the password of the last sheet if the last sheet was a
(code) password protected sheet before it displayed the sheet menu. Moving
the main sheet (which is not password protected) to the end was no great
drama although I would have preferred to keep the sheets in their original
sequence. If you can tell me why that is I would be very interested to know.
Thanks again for your help.
Regards,

 

[Mike Fogleman]

Tony, I am not having that issue in my test book. You may have some other
code in perhaps the ThisWorkbook code module or the Main worksheet module
that is being fired from an event before the UserForm shows. I have no code
in either of those modules and only 1 icon, on the Main sheet only, that
fires the SheetMenu sub in the general module. You can step through the code
line by line starting with the SheetMenu sub, and see where it takes you
that would ask for the last sheet password.
I only see one issue, and it may be exactly what you want, so I didn't
change it. Once you have a password sheet open, leave that sheet for
another, then come back, you need to re-enter the password. All your
Sheet_Activate and Sheet_Deactivate codes will fire whenever you change
worksheets. Just thought I'd throw that out there.

Mike F

 

[Guest]

Hiya Mike,

I've just come across this code of yours.

Can you tell me if it can ignore more than 1 ws and how, I tried to modify
it without success.

If ws.Name = "Main" Then

 

[Tim Williams]

If ws.Name = "Main" or ws.Name = "IgnoreMe" Then

Tim