Compile Error: Argument not optional - VB for applications 7 (EXCEL)

[jambo242]

I am in the process of creating a macro-enabled spreadsheet to manage multiple users. I have not 'programed' for a few years now. Would appreciate any help. Thank you.

I followed this Youtube video when creating my spreadsheet: https://www.youtube.com/watch?v=jGhkLeHMEUA

when I run the spreadsheet and enter my username and password I receive "Compile error: Argument not optional."

The erorr that is highlighted is (COUNTIF) in the following line:

lock_worksheet = Application.WorksheetFunction.CountIf(sh.Range("E" & user_row, "AH" & user_row) = "Ï")


My code is:


Private Sub btnLogin_Click()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("User Management")


If Me.txtUserName.Value = "" Then
MsgBox "Please enter your User Name", vbCritical
Exit Sub

If Me.txtPassword.Value = "" Then
MsgBox "Please enter your password", vbCritical
Exit Sub
End If '*******************think this was missing



If Application.WorksheetFunction.countiif(sh.Range("A:A"), Me.txtUserName.Value) = 0 Then
MsgBox "Invalid User name", vbCritical
Exit Sub

End If

Dim usr_row As Integer
user_row = Application.WorksheetFunction.Match(Me.txtUserName.Value, sh.Range("A:A"), 0)

If Me.txtPassword.Value <> CStr(sh.Range("C" & user_row).Value) Then
MsgBox "Invalid password", vbCritical



Exit Sub
End If


Dim lock_worksheet, unlock_worksheet As Integer

lock_worksheet = Application.WorksheetFunction.CountIf(sh.Range("E" & user_row, "AH" & user_row) = "Ï")
unlock_worksheet = Application.WorksheetFunction.CountIf(sh.Range("E" & user_row, "AH" & user_row) = "Ð")

If (lock_worksheet + unlock_worksheet) = 0 Then
MsgBox "You don't have the access for any worksheet, please contact Joe Todd", vbCritical
Exitsub
End If

'apply setting

Dim wsh As Worksheet
Dim i As Integer

If sh.Range("B" & user_row).Value = "Admin" Then ' for admin
sh.Unprotect 1234
sh.Cells.EntireColumn.Hidden = False
sh.Cells.EntireRow.Hidden = False

ThisWorkbook.Unprotect 1234
For Each wsh In ThisWorkbook.Worksheets
wsh.Visible -xlSheetVisible
wsh.Unprotect 1234
Next wsh
ActiveWindow.DisplayWorkbookTabs = True


Else 'for user
ThisWorkbook.Unprotect 1234
ActiveWindow.DisplayWorkbookTabs = True

For i = 5 To Application.WorksheetFunction.CountA(sh.Range("2:2"))
Set wsh = ThisWorkbook.Sheets(sh.Cells(2, i).Value)
If sh.Cells(user_row, i).Value = "Ð" Then ''for unlock
wsh.Visible = xlSheetVisible
wsh.Unprotect 1234
ElseIf sh.Cells(user_row, i).Value = "Ï" Then 'for unlock
wsh.Visible = xlSheetVisible
wsh.Protect 1234
End If

Next i
ThisWorkbook.Unprotect 1234
sh.Visible = xlSheetVeryHidden
End If


Unload Me

End Sub

Private Sub btnResetPassword_Click()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("User Management")


If Me.txtUserName.Value = "" Then
MsgBox "Please enter your User Name", vbCritical
Exit Sub

If Me.txt_password.Value = "" Then
MsgBox "Please enter your password", vbCritical
Exit Sub


If Application.WorksheetFunction.countiif(sh.Range("A:A"), Me.txtUserName.Value) = 0 Then
MsgBox "Invalid User name", vbCritical
Exit Sub

End If

Dim usr_row As Integer
user_row = Application.WorksheetFunction.Match(Me.txtUserName.Value, sh.Range("A:A"), 0)

If Me.txtPasword.Value <> CStr(sh.Range("C" & user_row).Value) Then
MsgBox "Invalid password", vbCritical
Exit Sub
End If

With frmResetPassword
.txtUserName.Value = Me.txtUserName.Value
.txt_user_row.Value = user_row
.Show False



End With

Unload Me

End Sub

Private Sub UserForm_Activate()

Dim sh As Worksheet
Dim wah As Worksheet

Set sh = ThisWorkbook.Sheets("User Management")

ThisWorkbook.Unprotect 1234

sh.Visible = xlSheetVisible

For Each wsh In ThisWorkbook.Worksheets
If wsh.Name <> "User Management" Then
wsh.Visible -xlSheetVeryHidden
End If
Next wsh

sh.Unprotect 1234
sh.Cells.EntireColumn.Hidden = True
sh.Cells.EntireRow.Hidden = True
sh.Protect 1234

ThisWorkbook.Protect 1234
activewidnow.DisplayWorkbookTabs = False


End Sub

[Zvoni]

....and don't cross-post
https://www.vbforums.com/showthread....11#post5669411

Nevermind it's VBA, and not .NET

[jdc2000]

May be this:

lock_worksheet = Application.WorksheetFunction.CountIf(sh.Range("E" & user_row & ":AH" & user_row) = "Ï")