Hello there,
Actually I have found this code and it does what I need but there's 1 problem that if you use a tool like kutools and click the option of unhide all sheets then all sheets will be hidden so what I need to add to the following code is when the username and password are correct in addition of visible the sheet which required the sheet to be unprotected so after visible the sheet I need to protect workbook structure again... so how I can perform that in the following code:
this code placed in THISWORKBOOK![]()
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 ThisWorkbook.ProtectStructure = True Or ThisWorkbook.ProtectWindows = True Then ThisWorkbook.Unprotect WPwd Sh.Activate If Sh.Name <> "WELCOME SCREEN" 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 4 '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 idea of the code is to create WELCOME SCREEN sheet and User List sheet which contain a table with username + password + sheet1 + sheet2 + .... as columns
under the sheet1, sheet2 I just insert "x" to let the sheet visible for the user or "" empty if I want the sheet to not be visible for the user, the only problem that the hidden sheet can be visible by kutools or maybe another tools when you click hide/unhide all sheets which make everything useless and weak.
Thanks in Advanced











LinkBack URL
About LinkBacks
Register To Reply

Bookmarks