new user having trouble with subscript on VB - error 9 : subscript out of range

[mattyd3081]

hello

i am trying to self teach Excel skills to help me with work .....

i have followed an online "how to" and have encountered an issue and cannot progress as there is no guidance on how to rectify my problem

any help you can provide would be great - im very new to excel so apologies if i am making a simple mistake.

bold text is where error shows with yellow highlight



Sub CheckUser()
Dim UserRow, SheetCol As Long
Dim SheetNm As String
With Sheet1
.Calculate
If .Range("B8").Value = Empty Then 'Incorrect Username
MsgBox "Please enter a correct username"
Exit Sub
End If
If .Range("B7").Value <> True Then 'Incorrect Password
MsgBox "Please enter a correct password"
Exit Sub
End If


UserRow = .Range("B8").Value 'User Row
For SheetCol = 7 To 18
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



thanks for your help

 

[Scott T]

Make sure SheetNm is correct If you try to hide a sheet that does not exist you will get that error.

 

[rlv01]

Also look at UserRow and/or SheetCol. For example you define UserRow as

 UserRow = .Range("B8").Value 'User Row

But if the cell value for B8 is "dog", then you are going to get an error for

If .Cells(UserRow, SheetCol).Value = "x" Then

If you know how to use the debugger, you can single step your way though the code and watch each variable as it executes.