Trigger UserForm for PW Entry on Change Event

[nau2002]

Hello,

I have a 'Navigation' sheet used to move around a large workbook. Essentially, the user selects where they want to go and then a v-lookup determines the appropriate sheet/cell based on a v-lookup hidden on the sheet. One of the sheets require a password to view so I'm trying to use a user-form (userform1). I found some code to do that but don't know how to make it run within the change event. Here's what I have:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lookupVal As String
    Dim lookupSheet As String
    Dim lookupCell As String
    Dim myCell As Range
  
'  #1  Only run if cell E7 or M7 is updated
    If Target.Address = "$E$7" Or Target.Address = "$M$7" Then
'       Capture value to lookup
        lookupVal = Target.Value
'       Find value in column D
        Columns("D:D").Find(What:=lookupVal, After:=Range("D15"), LookIn:=xlFormulas, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
'       Get corresponding values from column E and F
        lookupSheet = ActiveCell.Offset(0, 1)
        lookupCell = ActiveCell.Offset(0, 2)
'       Set range to go to
        Set myCell = Sheets(lookupSheet).Range(lookupCell)
'       Goto proper sheet/address
        Sheets(lookupSheet).Activate
        myCell.Select
    End If
    
    '#2 Access to PW Protected Sheet
    Application.EnableEvents = False
    Application.EnableEvents = True
       Dim msg As String
 Dim pass As String
 If Target.Address = "$M$7" Then
 If Target.Value = "Scorecard - Reporting & Administration" Then
 
 
' Public cntr As Integer
Private Sub UserForm_Activate()
' Set the counter to 0 when the User Form is activated
    cntr = 0
' Set the caption on the User Form
  UserForm1.Caption = "Password Required"
' Set the password character for textbox1 to an asterisk
TextBox1.PasswordChar = "*"
' Set the caption for the CommandButtons
CommandButton1.Caption = "Open Admin Sheet"
CommandButton2.Caption = “Cancel”
End Sub

Private Sub CommandButton1_Click()
' Call sub to validate the password entry
    ValidatePWD
End Sub

Private Sub ValidatePWD()
' Validate the password entry.
If TextBox1.Value = "invest" Then
    Worksheets("Administration").Visible = True
    Sheets("Administration").Activate
        UserForm1.Hide
   Else
    ' Increment the counter by one
    TextBox1.Value = ""
    TextBox1.SetFocus
        cntr = cntr + 1
       ' Check to see if the user has unsuccessfuly
        ' entered an incorrect password
        ' more than three times. If so, display a
        ' message box and close the workbook.
        If cntr > 2000 Then
            MsgBox "Sorry...wrong password...goodbye"
            ActiveWorkbook.Save
            'ThisWorkbook.Saved = True
            ThisWorkbook.Close
        Else
            ' Warn user that username and/or password is incorrect
            MsgBox "             Attempt #" & cntr & vbCrLf & _
                "Incorrect Password entered"
        End If
End If
End Sub

Private Sub CommandButton2_Click()
   Unload Me
End Sub
 
 
 
 'pword = Application.InputBox("Enter password to view 'Administration' tab.", "Password Required")
 'If pword = "invest" Then
 'Sheet25.Visible = True
 
 
 
Application.EnableEvents = True
    End If
    
    End If
    End If
End Sub

 

[cerfani]

what exactly is the problem? UserForm1.Show... shows it so you just put that wherever you need to show... in the change event... check whatever you must on the target range to determine if you should show the form.

Excel VBA Userform - EASY Excel Macros

 

[nau2002]

Sorry, I get an error message, 'Expected End Sub' between these two lines:

' Public cntr As Integer
Private Sub UserForm_Activate()

If I remove the "'" in front of the first line so the code reads like this:

Public cntr As Integer
Private Sub UserForm_Activate()

I get an error: Compile Error: Invalid attribute in Sub or Function

 

[cerfani]

the error is telling you your syntax is bad...

If you have this code...

Sub Hey()
    x = 1 + 1
End Sub

you are fine... if you have this...

Sub Hey()
    x = 1 + 1

you get the error you describe, that is what your problem is

if you want to make a new subroutine... first type the Sub and end sub part first... THEN put code inside it

also you have to declare variables inside the subroutine... this is vba (vb6 or vb7 is the language) not VB.Net