prompt for password to open different sheets

N

ndendrinos

Well-known Member

Joined

Jan 17, 2003

Messages

1,694

• May 18, 2010

• #2

Hello,
To assign different passwords to worksheets insert this to each worksheet code changing the password as you wish (in this example the password is "alpha":

Code:

 Private Sub Worksheet_Activate()
    Cells.Select
    Selection.EntireRow.Hidden = True
    P = InputBox("enter password")
    If P = "alpha" Then
    Cells.Select
    Selection.EntireRow.Hidden = False
   
    ElseIf P <> "alpha" Then
    MsgBox "That password is incorrect, taking you to Sheet1"
    
    Sheets("Sheet1").Select
    
      End If
End Sub


Private Sub Worksheet_Deactivate()
    Me.Cells.EntireRow.Hidden = True
End Sub

Hope this helps

 

Upvote 0

Smitty

Legend

Joined

May 15, 2003

Messages

29,536

• May 18, 2010

• #3

If you PM me your e-mail address I'll send you a workbook that lets you assign sheet access and passwords to individual users. I can also send one that assigns level passwords without user names. Both have dashboard interfaces.

Note that I'll be offsite until Sunday and might not respond right away.

 

Upvote 0

S

SimonPerry

New Member

Joined

Jun 10, 2006

Messages

47

• May 19, 2010

• #4

Thank you both so much. I will try the code suggested shortly and then let you know how I get on.

I'll also pm you Smitty as would love to see the example workbooks you mentioned.

Thanks again
Simon

 

Upvote 0

S

SimonPerry

New Member

Joined

Jun 10, 2006

Messages

47

• May 19, 2010

• #5

Ndedrino's - hi again. Can you explain the very bottom bit of code for me? I get the bulk bit of code but this bottom part of me.cells is confusing me...

Thanks again

 

Upvote 0

N

ndendrinos

Well-known Member

Joined

Jan 17, 2003

Messages

1,694

• May 19, 2010

• #6

Hello Simon,
There are several ways to write the code and this particular coder decided to use the "Me.Cells"
Instead he could have written it like this :

Code:

 Private Sub Worksheet_Deactivate()
Cells.Select
    Selection.EntireRow.Hidden = True
End sub

The gist is that upon deactivation of the worksheet all cells are hidden so that next time when the operator clicks on that tab he cannot see what's on the sheet unless the proper password is entered and all cells become visible.

 

Upvote 0

S

SimonPerry

New Member

Joined

Jun 10, 2006

Messages

47

• May 20, 2010

• #7

Ah ha, I'm with you now. Cheers again for your help, very much appreciated.

 

Upvote 0

C

Cemslie87

New Member

Joined

Mar 25, 2015

Messages

15

• Mar 26, 2015

• #8

hi i appreciate this is a very old thread but the line of code Selection.EntireRow.Hidden = True gives me a run time error.

can anyone please advise?

 

Upvote 0

Smitty

Legend

Joined

May 15, 2003

Messages

29,536

• Mar 26, 2015

• #9

Welcome to the Board!

Try without selecting:

Cells.EntireRow.Hidden

HTH,

 

Upvote 0