Userform with pasword

[Rob33]

I have a workbook that has the tabs hidden and people use buttons to navigate around the spreadsheet - I want to create a userform that when a person clicks the button to go to say sheet2, it asks them to key in a password before they can enter into sheet2 - if they key in the incorrect password - it just leaves them in the current sheet!!!



Regards,


ROB!!

 

[Fat Cat]

These thoughts might help,

Create a little UserForm with a Label1, Label2, TextBox1 and CommandButton1

Under the Properties for Label1 set the Caption = "Enter Password for sheet: "
Under the Properties for Label2, delete any caption

Under the properties for TextBox1 set the PasswordChar to *

Then double click on the TextBox1 and paste in the following code;

Private Sub TextBox1_AfterUpdate()
Dim myPass(1 To 3) As Variant
Dim whichSheet As String

myPass(1) = ""
myPass(2) = "sillybugger"
myPass(3) = "youfool"

    Select Case Label2.Caption            'Use the Label2 caption to figure out which sheet to go to
    Case "Sheet2"                     'if its sheet2, check the password for sheet2 and go there if ok
        If TextBox1.Text = myPass(2) Then
            Sheets("Sheet3").Visible = False
            Sheets("Sheet2").Visible = True
            Sheets("Sheet2").Activate
        End If
    Case "Sheet3"
        If TextBox1.Text = myPass(3) Then
            Sheets("Sheet2").Visible = False
            Sheets("Sheet3").Visible = True
            Sheets("Sheet3").Activate
        End If
    End Select
    TextBox1.Text = ""
    UserForm1.Hide

End Sub

Private Sub UserForm_Activate()
    TextBox1.SetFocus
End Sub

For each of your buttons to navigate around the worksheets enter the code;

UserForm1.Label2.Caption = "Sheet2"   'change to the name of the sheet you want to go to etc
UserForm1.Show

 

[Rob33]

USERFORM PASSWORD

i modified you code slightly - i think i have missed something out??? - i can get the userford to come up - Label 2 in the userform = ESTIMATE but when i type a password nothing seems to happen????

Private Sub TextBox1_AfterUpdate()
Dim myPass(1 To 3) As Variant
Dim whichSheet As String

myPass(1) = ""
myPass(2) = "estimate"
myPass(3) = "Bank"

Select Case Label2.Caption 'Use the Label2 caption to figure out which sheet to go to
Case "ESTIMATE" 'if its sheet2, check the password for sheet2 and go there if ok
If TextBox1.Text = myPass(2) Then
Sheets("ESTIMATE").Select
End If
Case "BANK"
If TextBox1.Text = myPass(3) Then
Sheets("Bank").Select
End If
End Select
TextBox1.Text = ""
Password.Hide

End Sub

Private Sub UserForm_Activate()
TextBox1.SetFocus
End Sub

 

[Fat Cat]

Try this;

Private Sub TextBox1_AfterUpdate()
Dim myPass(1 To 3) As Variant
Dim whichSheet As String

myPass(1) = ""
myPass(2) = "estimate"
myPass(3) = "bank"

    Select Case Label2.Caption
    Case "ESTIMATE"
        If TextBox1.Text = myPass(2) Then
            Sheets("BANK").Visible = False
            Sheets("ESTIMATE").Visible = True
            Sheets("ESTIMATE").Activate
        End If
    Case "BANK"
        If TextBox1.Text = myPass(3) Then
            Sheets("ESTIMATE").Visible = False
            Sheets("BANK").Visible = True
            Sheets("BANK").Activate
        End If
    End Select
    TextBox1.Text = ""
    UserForm1.Hide

End Sub

Private Sub UserForm_Activate()
    TextBox1.SetFocus
End Sub

Note: I couldn't get .Select to work on a hidden sheet, hense the use of setting .Visible to TRUE and then using .Activate to switch to that sheet.

For the CommandButtons calling the UserForm, use the syntax;

Private Sub CommandButton1_Click()
UserForm1.Label2.Caption = "BANK"
UserForm1.Show
End Sub

and

Private Sub CommandButton1_Click()
UserForm1.Label2.Caption = "ESTIMATE
UserForm1.Show
End Sub

 

[Rob33]

ADVERTISEMENT

userform password

Still not working?????


I think it is due to the hiden sheets??

if, instead of hiding the sheets i just select in "tools" "options" that sheet tabs are hidden (therefore by-passing the need to try unhide each individual sheet to view it)

does that make it easier - then all i need is if the pasword is correct then it can just .select the sheet (say - ESTIMATE) to go to


Regards
Rob

 

[Fat Cat]

Not sure, haven't tried that, but it should work

 

[Rob33]

USERFORM PASSWORD



How can i simplify this one - lets say all i want to do is click on a button to go to a sheet named "ESTIMATE" - I want a pop up box that asks for one password - if it is correct then i would go to the sheet named "ESTIMATE" if the password is entered incorrect the pop up box disappears and i stay on the same sheet that i am currently in!!!


HELP!!!!