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


"Mike Fogleman" wrote:

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
"Tony Borg" wrote in message
...
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
On Error GoTo 0
End Sub



"Mike Fogleman" wrote:

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
"Tony Borg" wrote in message
...
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.
--
TonyB