MrExcel Publishing
Your One Stop for Excel Tips & Solutions

I got lost in the shuffle.


Posted by tony on March 21, 2001 6:23 AM

I posted a request for help awhile back and it seems to have gone un-noticed. So, with hope that someone might be able to help me, I've copied the message and I am pasting it in here:

Here is another question that I need an answer to.
I have a workbook with seven sheets,to which I have
assigned each sheet their individual password. What
happens now is this: When an authorized (with
password)user tries to go into another sheet, for which
they do not have a password, and they press cancel at
password dialogue) box then it takes them on to the
next sheet where a password is asked for that sheet,
and when cancel is pressed it takes the user on to the
next sheet, and so forth. my question is this:
Is there a way to have it so as that the user will be
looped back to their own authorized sheet?
Here is a copy of one of the sheet's VBA Code:

Private Sub Worksheet_Activate()
Dim Reply As String
Reply = InputBox("Please supply the password for " & Me.Name, "Security")
If Reply <> "springfield" Then
Me.Previous.Select
End If
End Sub
Private Sub Workbook_Open()
Sheet1.Select
End Sub

Again, I appreciate any help.

Tony


Posted by mseyf on March 21, 2001 11:05 AM

I'm kind of a VBA novice, but you might try setting up a Public string variable to hold the name of a successfully opened sheet, then if a password is entered incorrectly, you return to that sheet

Private Sub Worksheet_Activate()
Dim Reply As String
Reply = InputBox("Please supply the password for " & Me.Name, "Security")
If Reply <> "springfield" Then
Sheets("StartSheet").Select
Exit Sub
End If
StartSheet = ActiveSheet.Name
End Sub

Public StartSheet as String
Private Sub Workbook_Open()
Sheet1.Select
StartSheet = ActiveSheet.Name
End Sub

this is untested. I just threw it out since you've gotten no other responses.

HTH

Mark