Hello all!
I am having the following problem
"Set wsh = ThisWorkbook.Worksheets(sh.Cells(2, i).Value)"
I have included below the full code:
Any help would be greatly appreciated!![]()
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
Thank you











LinkBack URL
About LinkBacks
Register To Reply
Bookmarks