Thomas Kuriakose
Active Member
Respected Sirs,
Good day to you, I need your guidance on a task where we have multiple users working on a workbook.
We would like to give access for users to work on sheets as be below user list table. The "x" should have the sheet visible and * should not have the sheet visible. I am getting an error subscript out of range at line highlighted in the below clip. This is triggered on workbook open.
The
The workbook has also got a worksheet change event for Index tab (A2) which on selection of data validation also hides and unhide respective sheets. This selection is given to get pictures of respective department on drop down selection from Index tab (A2) as well as calculations in other tabs.
I feel the workbook open and the worksheet change are duplicated, but have different purpose.
Kindly guide on how we can use both the codes either with a user form to get results of access as well as hiding and in hiding sheets. Or what would be the correct way to go about this.
Thank you very much for your guidance,
with regards,
thomas
Good day to you, I need your guidance on a task where we have multiple users working on a workbook.
We would like to give access for users to work on sheets as be below user list table. The "x" should have the sheet visible and * should not have the sheet visible. I am getting an error subscript out of range at line highlighted in the below clip. This is triggered on workbook open.
Username | Password | Sheet1 | Sheet2 | Sheet3 | Sheet4 | Sheet5 | Sheet6 | Sheet7 | Sheet8 | Sheet9 | Sheet10 | Sheet11 | Sheet12 | Sheet13 | Sheet14 |
ANN | A1 | X | x | * | * | * | * | * | * | * | * | * | * | * | |
JEN | A2 | x | x | * | * | * | * | * | * | * | * | * | * | * | * |
CAT | A3 | x | * | * | * | x | * | * | * | * | * | * | * | * | * |
LOY | A4 | x | * | * | * | * | * | * | * | * | * | x | x | * | * |
SAD | A5 | x | * | * | x | * | * | * | * | * | * | * | * | * | * |
MEN | MEN | x | x | x | x | x | x | x | x | x | x | x | x | x | x |
The
Code:
Private Sub Workbook_Open()
Dim Sh As Worksheet
Dim UserName As String
Dim Password As String
Dim ThisCell As Range
Dim c As Long
For Each Sh In ThisWorkbook.Worksheets
If Sh.Name <> "Welcome" Then
Sh.Visible = xlSheetVeryHidden
End If
Next Sh
UserName = InputBox("Please enter your user name.")
Password = InputBox("Please enter password.")
For Each ThisCell In Sheets("User List").Range("A2:A" & Sheets("User List").Range("A65536").End(xlUp).Row)
If UCase(ThisCell.Value) = UCase(UserName) And UCase(ThisCell.Offset(, 1).Value) = UCase(Password) Then
MsgBox "Access Granted"
For c = 2 To 14
'This is the number of sheets from C1 to E1
If ThisCell.Offset(, c).Value <> "" Then
Sheets(Sheets("User List").Cells(1, c + 1).Value).Visible = xlSheetVisible
End If
Next c
Exit Sub
End If
Next ThisCell
MsgBox "Access Denied"
ThisWorkbook.Close
End Sub
The workbook has also got a worksheet change event for Index tab (A2) which on selection of data validation also hides and unhide respective sheets. This selection is given to get pictures of respective department on drop down selection from Index tab (A2) as well as calculations in other tabs.
I feel the workbook open and the worksheet change are duplicated, but have different purpose.
Kindly guide on how we can use both the codes either with a user form to get results of access as well as hiding and in hiding sheets. Or what would be the correct way to go about this.
Thank you very much for your guidance,
with regards,
thomas