Getting unexpected Compile error: Variable not defined

[JJFletcher]

Hello All,

I am getting an unexpected error when trying to use the form. Any Ideas would be appreciated

Error:

unexpected Compile error: Variable not defined This is at the Create Username and Password Section - (IN RED COLOR) and the error is highlited in Blue - iCnt =

There are three Columns in the sheet (Users_Data)

Name | Password | Sheet1 - Once the form works - I need to modify the form so that each entry that is made in the form the 3rd Column is automatically populated with the word "Sheet1"

Added a pic of the UserForm - below

Option Explicit
Dim iCounta As Integer
Private Sub cmbValidate_Click()
    Dim rUsers As Range, rPasses As Range, rSheets As Range
    Dim lUserRow As Long
    Dim wsName As Variant
    Dim wsArray As Variant
    With Worksheets("Users_Data").UsedRange
        Set rUsers = .Columns(1)
        Set rPasses = .Columns(2)
        Set rSheets = .Columns(3)
    End With
    If Application.WorksheetFunction.CountIf(rUsers, Me.tbxUser.Value) < 1 Then
        MsgBox "Invalid Username", vbExclamation, "Alert"
    Else
        lUserRow = Application.WorksheetFunction.Match(Me.tbxUser.Value, rUsers, False)
        If Not CStr(Me.tbxPW.Value) = CStr(rPasses.Rows(lUserRow).Value) Then
            MsgBox "Invalid Password", vbExclamation, "Alert"
        Else
            UnlockSheets (rSheets.Rows(lUserRow).Value)
            Unload Me
            Worksheets("Users_Data").Cells(lUserRow, 5).Value = Worksheets("Users_Data").Cells(lUserRow, 5).Value + 1
            End
        End If
    End If
    With Me
        .LblTries.Caption = .LblTries.Caption - 1
        tbxUser.Value = vbNullString
        tbxPW.Value = vbNullString
        tbxUser.SetFocus
    End With
    iCounta = iCounta + 1
    If iCounta > 2 Then
        MsgBox "3 Invalid Attempts. WorkBook Will Now Close", vbOKOnly + vbCritical, "Warning"
        ActiveWorkbook.Close savechanges:=False
    End If
End Sub

Private Function UnlockSheets(sShts As Variant)
    Dim sh As Variant
    On Error GoTo BadShts
    If sShts = "" Then GoTo BadShts
    
    For Each sh In Split(sShts, ",")
       With ThisWorkbook.Sheets(sh)
         .Visible = True
          .Select
        End With
    Next sh
    
    Worksheets("Users_Data").Visible = xlSheetVeryHidden

    MsgBox "Congratulations - " & sShts & " Unlocked", vbInformation
    On Error GoTo 0
    Exit Function
BadShts:
    MsgBox "Invalid Sheet Names : " & sShts, vbCritical
End Function


Private Sub Label5_Click()

End Sub

Private Sub UserForm_Initialize()
    Me.LblTries.Caption = 3

End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = 0 Then
        Cancel = True
        If Not MsgBox("Would You Like To Close The Document?", vbYesNo + vbInformation, "Close Request") = vbNo Then
            ActiveWorkbook.Close savechanges:=False
        End If
    End If
End Sub

'___________Create Username and Password Section______________________________________________________


'Variable Declaration
Dim BlnVal As Boolean


Private Sub UserForm_Initialize2()
    'Variable declaration
    Dim IdVal As Integer
    
    'Finding last row in the Data Sheet
    IdVal = fn_LastRow(Sheets("Users_Data"))
    

End Sub
Sub cmdAdd_Click()
    'Variable declaration
    Dim txtName, txtPassword
    'Dim iCnt As Integer
    
    'find next available row to update data in the data worksheet
    iCnt = fn_LastRow(Sheets("Users_Data")) + 1
     
    'Update userform data to the Data Worksheet
    With Sheets("Users_Data")
 
        .Cells(iCnt, 1) = frmData.txtName

        .Cells(iCnt, 2) = frmData.txtPassword
      
    'Finding last row in the Data Sheet
    IdVal = fn_LastRow(Sheets("Users_Data"))
    
     End With
End Sub


'In this example we are finding the last Row of specified Sheet
Function fn_LastRow(ByVal Sht As Worksheet)

    Dim lastRow As Long
    lastRow = Sht.Cells.SpecialCells(xlLastCell).Row
    lRow = Sht.Cells.SpecialCells(xlLastCell).Row
    Do While Application.CountA(Sht.Rows(lRow)) = 0 And lRow <> 1
        lRow = lRow - 1
    Loop
    fn_LastRow = lRow

End Function

'Exit from the Userform
Private Sub cmdCancel_Click()
    Unload Me
End Sub

' Check all the data(except remarks field) has entered are not on the userform
Sub Data_Validation()
     If txtName = "" Then
        MsgBox "Enter Name!", vbInformation, "Name"
        Exit Sub
    ElseIf txtCNum = "" Then
        MsgBox "Enter Password!", vbInformation, "Password"
        Exit Sub
    Else
        BlnVal = 1
    End If
End Sub

'Clearing data fields of userform
Private Sub cmdClear_Click()
    Application.ScreenUpdating = False
        txtName.Text = ""
        txtPassword = ""

    Application.ScreenUpdating = True
End Sub

[dotchiejack]

Hi,
Delete the apostrophe before this line of code

Dim iCnt As Integer

and it should work.

[JJFletcher]

Hi dotchiejack,

Thanks for your suggestion... Deleting the apostrophe as requested did let the code move further until another error …

This one is a bit lower : Compile error: Variable not defined.... IdVal = seems to be the spot

 'Finding last row in the Data Sheet
    IdVal = fn_LastRow(Sheets("Users_Data"))
    
     End With
End Sub

Regards,

John

[nigelog]

add

Dim idVal as Long

at top of that sub

[JJFletcher]

Thanks nigelog,

Your suggestion has allowed the code to move forward and then produce another compile error at...

Function fn_LastRow(ByVal Sht As Worksheet)

    Dim lastRow As Long
    lastRow = Sht.Cells.SpecialCells(xlLastCell).Row
    lRow = Sht.Cells.SpecialCells(xlLastCell).Row
    Do While Application.CountA(Sht.Rows(lRow)) = 0 And lRow <> 1
        lRow = lRow - 1
    Loop
    fn_LastRow = lRow

End Function

Also getting this when Resetting the code:


Regards,

John

[nigelog]

Dim lrow as long

and probably

fn_LastRow as Long

and there should be no code above the Dim BinVal as boolean if it is declared public

can you attach workbook??

[JJFletcher]

nigelog,

Here is the workbook...

The goal here is to allow the user to enter Username and Password and gain entry if already registered or allow the user to add a Username and Password if not registered.

The column that says Sheet1 I would like to automatically be entered there by default as each person enters their Username and password for the first time

[JJFletcher]

Loading up now

[JJFletcher]

Sorry - workbook was password protected - here is an open version

[nigelog]

Moving the Dim to top of page so

Option Explicit
Dim iCounta As Integer
Dim BlnVal As Boolean

got me into a locked sheet1

[JJFletcher]

Sorry nigelog,

The Test Workbook that I sent up Sheet1 is unlocked...hhhmmmmm

I can send again... Moving DIM as suggested eliminates the errors but does not *** a Username and Password in Users_Data sheet

Regards,

John

[bakerman2]

This works.

Private Sub cmbValidate_Click()

    Dim rUsers As Range, rPasses As Range, rSheets As Range
    Dim lUserRow As Long

    With Sheets("Users_Data").UsedRange
        Set rUsers = .Columns(1)
        Set rPasses = .Columns(2)
        Set rSheets = .Columns(3)
    End With
    If tbxUser = vbNullString Then Exit Sub
    If tbxPW = vbNullString Then Exit Sub
    If Application.CountIf(rUsers, tbxUser.Value) < 1 Then
        MsgBox "Invalid Username", vbExclamation, "Alert": GoTo gonext
    Else
        lUserRow = Application.Match(Me.tbxUser.Value, rUsers, 0)
        If Not CStr(tbxPW.Value) = CStr(rPasses.Rows(lUserRow).Value) Then
            MsgBox "Invalid Password", vbExclamation, "Alert": GoTo gonext
        Else
            UnlockSheets (rSheets.Rows(lUserRow).Value)
            Unload Me
            With Sheets("Users_Data")
                .Unprotect "jj"
                .Cells(lUserRow, 5).Value = Sheets("Users_Data").Cells(lUserRow, 5).Value + 1
                .Protect "jj"
            End With
            Exit Sub
        End If
    End If
gonext:
    With Me
        .LblTries.Caption = .LblTries.Caption - 1
        tbxUser.Value = vbNullString
        tbxPW.Value = vbNullString
        tbxUser.SetFocus
    End With
    iCounta = iCounta + 1
    If iCounta > 2 Then
        MsgBox "3 Invalid Attempts. WorkBook Will Now Close", vbOKOnly + vbCritical, "Warning"
        ActiveWorkbook.Close savechanges:=False
    End If
End Sub

[JJFletcher]

Hi Backerman2,

I replaced the code as suggested and this is what happens and also does not add username and password

[nigelog]

Im getting lost

what userform are you trying to operate "frnData" or "frmLogin"

[nigelog]

Bakermans code added to a revised workbook (got rid of cross references)

[bakerman2]

Clean workbook with most recent code.