Passwords for Hidden Worksheets

[Paul Black]

Hi Everyone,

I have a Workbook that will be Shared by 7 Users. Each User will have
a Specific Worksheet just for them.
I have Hidden the 7 Worksheets.
I have a Button on a Sheet Named "Logon" which I want Each User to
Click and Enter a Password that will Unhide JUST their Worksheet. I
would like a Macro that I can Attach to the Button that will Allow
this Please.
I Also have a Button on Each of the Worksheets, so when they have
Finished they will Click it and it will Re-Hide their Worksheet. I
would like a Macro to do this Please.
One Final thing, I need to be Able to Input a Master Password that
will Unhide ALL of the Hidden Worksheets Please.

Thanks for your Help in Advance.
All the Best
Paul

 

[ManualMan]

I have Hidden the 7 Worksheets.

Exactly how do you expect your 7 users not to unhide any sheet
manually?

I have a Button on a Sheet Named "Logon" which I want Each User to
Click and Enter a Password that will Unhide JUST their Worksheet. I
would like a Macro that I can Attach to the Button that will Allow
this Please.

How about this one? Curs

Sub HideAndShow()

UserName = Sheets("Logon").Cells(1,1) 'Replace (1,1) with the real
range
Password = Sheets("Logon").Cells(1,2) 'Replace (1,2) with the real
range


If Username = "Pete" And Password = "PetesPassword" Then
Sheets("PetesSheet").Visible = True
Else
MsgBox ("That really wasn't the right password!")
End If

End Sub

I Also have a Button on Each of the Worksheets, so when they have
Finished they will Click it and it will Re-Hide their Worksheet. I
would like a Macro to do this Please.

The same as above, only without the username/password validation, and
change Sheets(".....").Visible=True to Sheets("....").False

One Final thing, I need to be Able to Input a Master Password that
will Unhide ALL of the Hidden Worksheets Please.

I think you can make that happen using the code mentioned above.

 

[Norman Jones]

Hi ManualMan,

Perhaps, by setting the visible property of the sheets to xlVeryHidden?

Whilst this will not deter a knowledgeable user, sheets hidden in this
manner can only be unhidden via VBA.

 

[ManualMan]

Whilst this will not deter a knowledgeable user, sheets hidden in
this

manner can only be unhidden via VBA

Ahh! Finally, someone who uses the word "Whilst"! Hope the rest of my
previous post did help you, though..

MM

 

[Tom Ogilvy]

sheets hidden in this

manner can only be unhidden via VBA.

That is true in xl95 and earlier. In xl97, you can access the sheet
properties window in the VBE and unhide the sheet. Of course you could
protect the project to prevent this, but just some added information.

 

[Norman Jones]

Hi Tom,

Thank you for the precision.

My use of the *via VBA* terminology was too loose.

 

[Tom Ogilvy]

The fact that a user had to use code in xl5/95 was a major selling point of
Very Hidden. And It is often stated that it must be changed with VBA (based
on that previous restriction) - so I wasn't sure whether you were of that
belief or your use of VBA also included the manual capabilities in the VBE.
Apparently the latter. In any event, it should be clearer to the OP at
this point (if it wasn't before).

 

[Paul Black]

Thank you ALL very Much for your Replies.

I have Used the Code Posted by ManualMan and Added a Couple of things.
I Setup a Sheet where there are 7 Input Cells for Username and 7 Input
Cells for Password ( 1 for Each Group ).
I Set the Sheets ( the 6 I had Hidden ) Properties in the VB Editor to
xlVeryHidden, But when you Unhid them and then Hid them again you
could see the Sheets in Format > Sheet > Unhide, and Unhide the Sheets
from there.
Instead of Using :-

Sheets("Group 1").Visible = False

I Used :-

Sheets("Group 1").Visible = xlVeryHidden

this Worked Fine, and the Sheets could NOT then be Seen in Format >
Sheet > Unhide.
I Also Added a Bit of Code so that if the Password Entered was Either
Right OR Wrong, Once you Clicked the Button, the Cell in which the
Password was Entered Defaulted Back to a Blank, that way the Password
Can NOT be Viewed by Anyone Else.
Thanks to Everyone for their Time, I thought I would Post the Working
Code in Case Anyone Else can make Use of it.
I would Welcome Any Comments.
The Code Below is for "Group 1", I Repeated it for the Other "Groups"
:-

Sub Group_1_Login()
UserName = Sheets("Login & Logoff").Cells(2, 3)
Password = Sheets("Login & Logoff").Cells(3, 3)
If UserName = "Group 1" And Password = "Password" Then
Sheets("Group 1").Visible = True
Range("C3") = ""
Else
Range("C3") = ""
MsgBox "The Group 1 Password You Have Entered Is Invalid, Please Try
Again.", vbCritical, "Invalid Group 1 Password"
End If
End Sub

Sub Group_1_Finished()
Sheets("Group 1").Visible = xlVeryHidden
End Sub

Once Again, Thank You.
All the Best
Paul

 

[Paul Black]

Hi Everyone,

One Further thing I would like to do.
Each Groups Sheet is Protected with a Password and ALL Cells will be
"Locked" EXCEPT Cells "C4:H6", these are the ONLY Cells that Numeric
Values will be Entered.
What I would like is that when Each Group has Finished Inputting Data
and Clicks the Finished Button, the Cells with Data in ( Range "C4:H6"
) Become "Locked". Once the Finished Button is Clicked I do NOT want
them to be Able to Change ANY Previous Data Entered.

The Code for the "Group 1" Finished Button is :-

Sub Group_1_Finished()
Sheets("Group 1").Visible = xlVeryHidden
End Sub

One Final thing, there is One User that I have Setup so when he Logs
in it Unhides ALL the Sheets, and when he Clicks the Finished Button
it Hides ALL the Relevant Sheets. I want him to be the ONLY One who
can Change Data Entered, I would think it will need a Bit of Extra
Code in the Sub Master_Login to "Unlock" the Cells "C4:H6" in Each
Groups Sheet, and a Bit of Extra Code in the Sub Master_Finished to
"Lock" the Cells "C4:H6" in Each Groups Sheet with Data in. His Code
is :-

Sub Master_Login()

UserName = Sheets("Login & Logoff").Cells(32, 3)
Password = Sheets("Login & Logoff").Cells(33, 3)

If UserName = "Master" And Password = "Password" Then
Sheets("Group 1").Visible = True
Sheets("Group 2").Visible = True
Sheets("Group 3").Visible = True
Sheets("Group 4").Visible = True
Sheets("Group 5").Visible = True
Sheets("Group 6").Visible = True
Sheets("Results").Visible = True
Range("C33") = ""
Else
Range("C33") = ""
MsgBox "The Master Password You Have Entered Is Invalid, Please Try
Again.", vbCritical, "Invalid Master Password"
End If
End Sub

Sub Master_Finished()
Sheets("Group 1").Visible = xlVeryHidden
Sheets("Group 2").Visible = xlVeryHidden
Sheets("Group 3").Visible = xlVeryHidden
Sheets("Group 4").Visible = xlVeryHidden
Sheets("Group 5").Visible = xlVeryHidden
Sheets("Group 6").Visible = xlVeryHidden
Sheets("Results").Visible = xlVeryHidden
End Sub

Thanks to Everyone for their Help
All the Best
Paul

 

[ManualMan]

Hi there,

What I would like is that when Each Group has Finished Inputting Data
and Clicks the Finished Button, the Cells with Data in ( Range "C4:H6"
) Become "Locked". Once the Finished Button is Clicked I do NOT want
them to be Able to Change ANY Previous Data Entered.

This does the trick:

Single cell:
Cells(1, 1).Locked = True

Range:
Range(Cells(1, 1), Cells(10, 10)).Locked = True

Needles to say the other possible argument is: False.

Tip: Try recording a macro while performing the desired actions
manually, then check the code window, so you learn programming on the
fly!

Regards,
ManualMan
www.gamesXL.tk

 

[Paul Black]

Thanks for the Reply ManualMan,

I have tried Recording Macros as you Suggested but am Not Able to get
the Results I Require.
There are 6 Periods ( Months ) in Cells "C4:H6". C4, C5 & C6 are
Period 1, D4, D5 & D6 are Period 2 etc.
What I would Ideally like is that After they have Finished Inputing
Data for Period 1, and then Click the Finished Button, the Cells
"C4:C6" are "Locked" for Period 1, and then After Data has been
Entered for Period 2 those Cells are "Locked" etc.

Thanks for your Help.
All the Best
Paul