Password Userform Help Please

[Topher]

I have a workbook with multiple sheets. I want departments to view the
sheets applicable to that department. I have set up the hide and views but
wanted the 'trigger' to be a password entered in a password userform. My
userform code below but it will not work and I can't work our why. Any ideas?


Public PsWdAnswer As String
'Password for All Sheets
Private Const PsWd2 As String = "A"
'Password for Admin
Private Const PsWd3 As String = "B"
'Password for Operations
Private Const PsWd4 As String = "C"
'Password for Consult
Private Const PsWd5 As String = "D"
'Password for Marketing
Private Const PsWd6 As String = "E"

Private Sub Cancel2_Click()

Call Password1Exit

End Sub

Private Sub OKButton2_Click()

If PsWdAnswer = PsWd2 Then
Call Unhide_All
ElseIf PsWdAnswer = PsWd3 Then
Call Admin
ElseIf PsWdAnswer = PsWd4 Then
Call Operations
ElseIf PsWdAnswer = PsWd5 Then
Call Consult
ElseIf PsWdAnswer = PsWd6 Then
Call Marketing
Else: Call Password1Exit
End If
End Sub

Private Sub PsWd_Change()


End Sub

 

[Ryan H]

What do you mean by it won't work? Are you getting an error? The code is
doing something you aren't expecting? Be specific.

I assume PsWdAnswer is the name of a Textbox on your user form and I assume
you have this code located in the password userform module. If so, your code
should work. However, in your case I would think it would be more efficient
to use the Select Case statement rather than If...Thens.

Hope this helps! If so, let me know, click "YES" below.

Option Explicit

Public PsWdAnswer As String
'Password for All Sheets
Private Const PsWd2 As String = "A"
'Password for Admin
Private Const PsWd3 As String = "B"
'Password for Operations
Private Const PsWd4 As String = "C"
'Password for Consult
Private Const PsWd5 As String = "D"
'Password for Marketing
Private Const PsWd6 As String = "E"

Private Sub Cancel2_Click()
Call Password1Exit
End Sub

Private Sub OKButton2_Click()

Select Case PsWdAnswer.Text
Case Is = PsWd2: Call Unhide_All
Case Is = PsWd3: Call Admin
Case Is = PsWd4: Call Operations
Case Is = PsWd5: Call Consult
Case Is = PsWd6: Call Marketing
Case Else: Call Password1Exit
End Select

End Sub

 

[Topher]

Ryan,

You are right in all your assumptions, sorry i will try and be more
explicite in future.

Even using your code I get a compile error "Member already exists in an
object module from which this object derives". The compiler highlights the
first line of code "PsWdAnswer As String".

My original code did not call the macros for any of the responses.

Topher

 

[Ryan H]

Sorry, I should have noticed this. If PsWdAnswer is the name of the textbox
then you don't need this line. The error occurs because Excel is confused on
wiether PsWdAnswer is a string variable or a textbox.

' delete this line
Public PsWdAnswer As String

Hope this helps! If so, let me know, click "YES" below.