Abulean2010
New Member
- Joined
- Nov 28, 2021
- Messages
- 8
- Office Version
- 365
- Platform
- Windows
i have done this excel sheet with User level security in Excel Workbook for different worksheets by using this VBA Code
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.Protect 1234
Next
Else '''''for User Role
ThisWorkbook.Unprotect 1234
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 = "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
when enter the sheet by user log i have error 9 out of range
here
Set wsh = ThisWorkbook.Sheets(sh.Cells(2, i).Value)
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.Protect 1234
Next
Else '''''for User Role
ThisWorkbook.Unprotect 1234
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 = "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
when enter the sheet by user log i have error 9 out of range
here
Set wsh = ThisWorkbook.Sheets(sh.Cells(2, i).Value)
Ayman.xlsm | |||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | |||
1 | User Management | Worksheets | |||||||||||||||||||||||||||||||||||||
2 | User Name | Role | Password | All | Data Summary | Master DataSheet | WWTP Summary | General Information | Lab Results | Sludge Production Predicition | Abu Nusair WWTP | Kufranjeh WWTP | Aqaba Natural WWTP | Aqaba Mechanical WWTP | Baqaa WWTP | Central Irbid WWTP | Muta Al Mazar WWTP | Al Samra WWTP | Ramtha WWTP | Maan WWTP | Madaba WWTP | Wadi Musa WWTP | Fuhais WWTP | Merad WWTP | Wadi Hassan WWTP | Wadi Esser WWTP | Wadi Arab WWTP | South Amman WWTP | Tal Al Mantah WWTP | Tafilah WWTP | Shallalah WWTP | Salt WWTP | Mazar WWTP | Lab Results | |||||
3 | Ayman | Admin | **** | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | |||||
4 | Salam | Admin | **** | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | |||||
5 | Suhaib | Admin | **** | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | Ð | |||||
6 | Nadine | Admin | **** | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | Ï | |||||
7 | Noor | User | **** | x | x | Ï | Ï | x | x | Ï | x | x | x | x | x | x | x | x | x | x | x | x | x | x | x | x | x | x | x | x | x | x | x | x | |||||
8 | |||||||||||||||||||||||||||||||||||||||
9 | |||||||||||||||||||||||||||||||||||||||
10 | |||||||||||||||||||||||||||||||||||||||
11 | |||||||||||||||||||||||||||||||||||||||
12 | |||||||||||||||||||||||||||||||||||||||
13 | |||||||||||||||||||||||||||||||||||||||
14 | |||||||||||||||||||||||||||||||||||||||
User Management |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
D3:AH32 | Cell Value | ="Ð" | text | NO |
D3:AH32 | Cell Value | ="Ï" | text | NO |
D3:AH32 | Cell Value | ="x" | text | NO |
Last edited: