Hello all!
I am having the following problem
"Set wsh = ThisWorkbook.Worksheets(sh.Cells(2, i).Value)"
I have included below the full code:
Private Sub CommandButton1_Click()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("User Management")
If Me.txt_UserName.Value = "" Then
MsgBox "Please enter the User name", vbCritical
Exit Sub
End If
If Me.txt_Password.Value = "" Then
MsgBox "Please enter the password", vbCritical
Exit Sub
End If
If Application.WorksheetFunction.CountIf(sh.Range("A:A"), Me.txt_UserName.Value) = 0 Then
MsgBox "User name does not exists", vbCritical
Exit Sub
End If
Dim user_row As Integer
user_row = Application.WorksheetFunction.Match(Me.txt_UserName.Value, sh.Range("A:A"), 0)
If CStr(sh.Cells(user_row, 3).Value) <> Me.txt_Password.Value Then
MsgBox "Invalid password", vbCritical
Exit Sub
End If
'''''''''''''' Check the Worksheet Access '''''''''''''''
Dim lock_worksheet, unlock_worksheet As Integer
lock_worksheet = Application.WorksheetFunction.CountIf(sh.Range("D" & user_row, "XFD" & user_row), "Ï")
unlock_worksheet = Application.WorksheetFunction.CountIf(sh.Range("D" & user_row, "XFD" & user_row), "Ð")
If sh.Cells(user_row, 2).Value <> "Admin" Then
If (lock_worksheet + unlock_worksheet) = 0 Then
MsgBox "You don't have the access for any worksheet, please contact with admin", vbCritical
Exit Sub
End If
End If
''''''''''''' Apply setting ''''''''
Dim wsh As Worksheet
Dim i As Integer
If sh.Cells(user_row, 2).Value = "Admin" Then '''' Admin role
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
Else '''''for User Role
ThisWorkbook.Unprotect 1234
For i = 5 To Application.WorksheetFunction.CountA(sh.Range("2:2"))
Set wsh = ThisWorkbook.Worksheets(sh.Cells(2, i).Value)
If sh.Cells(user_row, i).Value = "x" Then
wsh.Visible = xlSheetVeryHidden
ElseIf sh.Cells(user_row, i).Value = "Ð" Then
wsh.Visible = xlSheetVisible
wsh.Unprotect 1234
ElseIf sh.Cells(user_row, i).Value = "Ï" Then
wsh.Visible = xlSheetVisible
wsh.Protect 1234
End If
Next i
sh.Visible = xlSheetVeryHidden
ThisWorkbook.Protect 1234
End If
ActiveWindow.DisplayWorkbookTabs = True
Unload Me
End Sub
Any help would be greatly appreciated!
Thank you
Bookmarks