Show & hide Sheets - depending on true/false message input.

Posted by Phil on June 27, 2001 1:58 AM

I have a shared work book that is accessed by every1 in the company... However only one sheet is relivant to 99% of them. So what i would like to do is create a macro that has a msg box popup on open and asks them to type in their password if they type in the word other than "password" i want it to hide all the sheets apart from one called "DRUMS". If they type in the word "password" i would like it to show all the sheets.

Any1 know how that could be done...????

Cheers

Phil

Posted by Dax on June 27, 2001 2:38 AM

Phil,

You can do this by putting code into the workbook module. Open the VB Editor (alt+F11), click View, Project Explorer if you can't see it already and then double click the ThisWorkbook icon. Some code like this should do what you want.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Hide all sheets as "very hidden" i.e. can't be unhidden using
'Format, Sheet, Unhide.
Dim sht As Object
For Each sht In ThisWorkbook.Sheets
If sht.Name &LT;&GT; "Drums" Then
sht.Visible = xlSheetVeryHidden
End If
Next
End Sub

Private Sub Workbook_Open()
Dim pass As String, sht As Object
pass = InputBox("Please enter password if you wish to view all sheets", _
"Password")
If pass = "password" Then
For Each sht In ThisWorkbook.Sheets
sht.Visible = xlSheetVisible
Next
End If
End Sub

HTH,
Dax.

Posted by Phil on June 27, 2001 2:55 AM

Thanxs - it worked 1st time....

but, do u know if it is possible to make the letters in the msgBox stared out like ******** instead of the letter.

Cheers

Phil

Posted by Dax on June 27, 2001 3:14 AM

You can't make the inputbox display * rather than the actual characters typed. What you could do is create a simple userform with a textbox on it and carry out the same sheet hiding procedures. A textbox can be set to display * as the user types.

Regards,
Dax.

Posted by Phil on June 27, 2001 3:39 AM

I've made the UserForm but how do i connect the VB Code to it...?

If u email me an excel sheet with 1 already on i work it out

Cheers

phil