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

[jambo242]

I tried to use this Youtube video to create a macroenabled spreadsheet (ExceL): https://www.youtube.com/watch?v=jGhkLeHMEUA

I have not coded for a few years and would appreciate any help.

Thank you.


When I run the spreadsheet, it breaks and highlights this line of code:


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



My code:

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]

Use Code-Tags

As for your error:

https://learn.microsoft.com/en-us/of...nction.countif

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

Note the comma before the "I"