Type mismatch (Error 13)

[helplessnoobatexcel]

Hi All, I have this error in my code Type Mismatch (Error 13) that I am unable to rectify. Any idea on how I can do so? Btw I moused over the error during debugging and found my UserRow to be "".

 

[helplessnoobatexcel]

Are they EXACT?

The error suggests that the VBA can't find a sheet that is stored in 'SheetNm' at the time of the error. Hover over 'SheetNm' when you get the error and see what it is.

Ohhh oops haha it wasn't one of the sheets names was wrong. I applied ur previous solution of removing the iferror in my formula however I realise it wld cause more problems further on in the code. It now brings me back to my original problem which I still can't rectify.

 

[Georgiboy]

Are you leaving the textbox on the form empty?

 

[helplessnoobatexcel]

Are you leaving the textbox on the form empty?

Yep, it has to be left empty. It only displays a numerical value correlating to the user's name (row position) when the user types in his username in the login box.

 

[Georgiboy]

If you run your code when the '5' is in cell B12 then UserRow should be 5 and not ""

I am confused now, where are you getting your error currently?

If you paste another picture, include the column letters and row numbers. Also paste the code between code tags (Use the VBA button in the editor and paste between the code tags

 

[helplessnoobatexcel]

If you run your code when the '5' is in cell B12 then UserRow should be 5 and not ""

I am confused now, where are you getting your error currently?

If you paste another picture, include the column letters and row numbers. Also paste the code between code tags (Use the VBA button in the editor and paste between the code tags

Yep my formula works correctly in the cell which is why I am confused.

Sub CheckUser()
Dim UserRow, SheetCol As Long
Dim SheetNm As String
With Sheet11
.Calculate

If .Range("B12").Value = Empty Then 'Incorrect Username'
MsgBox "Please enter a correct user name"
Exit Sub
End If

If .Range("B11").Value <> True Then 'Incorrect Password'
MsgBox "Please enter a correct password"
Exit Sub
End If

LoginForm.Hide
.Range("B9,B10").ClearContents
UserRow = .Range("B12").Value 'User Row'

For SheetCol = 6 To 16
SheetNm = .Cells(4, SheetCol).Value 'Sheet Name'
If .Cells(UserRow, SheetCol).Value = "Ð" Then
Sheets(SheetNm).Unprotect "123"
Sheets(SheetNm).Visible = xlSheetVisible
End If
If .Cells(UserRow, SheetCol).Value = "Ï" Then
Sheets(SheetNm).Protect "123"
Sheets(SheetNm).Visible = xlSheetVisible
End If

If .Cells(UserRow, SheetCol).Value = "x" Then Sheets(SheetNm).Visible = xlVeryHidden
Next SheetCol
End With
End Sub

 

[Georgiboy]

What line are you getting an error on currently?

Try changing:

If .Range("B12").Value = Empty Then 'Incorrect Username'

to:

If .Range("B12").Value = "" Then 'Incorrect Username'

 

[helplessnoobatexcel]

What line are you getting an error on currently?

Try changing:

If .Range("B12").Value = Empty Then 'Incorrect Username'

to:

If .Range("B12").Value = "" Then 'Incorrect Username'

Hi George, I've tried your suggestion but it doesn't seem to make a difference.
I'm still facing the same problem for my line of code of:
If .Cells(UserRow, SheetCol).Value = "Ð" Then
it still displays UserRow=""

 

[Georgiboy]

Is the image of the sheet you are pasting named 'Sheet11' or is the codename 'Sheet11' or both?

Are you able to upload your workbook and share it on OneDrive for example?

 

[helplessnoobatexcel]

Is the image of the sheet you are pasting named 'Sheet11' or is the codename 'Sheet11' or both?

Are you able to upload your workbook and share it on OneDrive for example?

Hi George, it is the codename 'Sheet11' as it would be more convenient should I change the name of the sheet.
I have excellent news! I have managed to rectify the problem and the code now works like a charm. The problem was
LoginForm.Hide
.Range("B9,B10").ClearContents
as it would automatically clear the contents of the cell which was why UserRow displayed "". I have now removed these two lines and it works! However, I have a minor problem as now it would not automatically clear my login form contents and it will display the previous user's credentials should I attempt to re-login. Any suggestions on what I can do? Btw nope I am unable to upload my workbook as it is highly confidential and can only be shared within my organization. Thanks alot for the help you have provided so far George!

 

[Georgiboy]

Great news, I think that would be more an issue with the userform. I suggest starting a new thread with your new issue.