Automatically Display Worksheets of Specific User

How to automatically display the worksheets of a specific user with a password using an inputbox and INSTR function.
Earlier we had learnt how to display a single worksheet of a specific user using a password. When we have multiple users working on a workbook on a specific project we can allow the users to access the relevant data and their specific worksheets so that they can complete their project quickly and easily. In this video we learn how to extract a string from the worksheet name and compare it with the password we enter into an input-box. We can make the algorithm even more complex to prevent others from guessing the password. We can also use a user-form with a text-box where we can set the properties of the characters entered into the text-box as asterisks so that others cannot view the entered password. This was demonstrated in an earlier video.

Watch the video below to understand how the displaying of hidden worksheets for a specific user is implemented:

Watch this video on YouTube.

Her’s the complete VBA code:

Private Sub Workbook_Open()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
On Error Resume Next
wks.Visible = xlSheetHidden
If InStr(wks.Name, “Ref”) Then
wks.Visible = xlSheetVisible
End If
Next wks
‘UserForm1.Show
End Sub

Sub enterPassword()
Dim mypassword As String
Dim wks As Worksheet
mypassword = InputBox(“Please enter your password”, “Enter Password”)
If mypassword = “” Then
MsgBox “No password entered. Exiting sub!”
Exit Sub
End If

For Each wks In ActiveWorkbook.Worksheets
If InStr(wks.Name, mypassword) Then
wks.Visible = xlSheetVisible
End If
Next wks

End Sub

Further reading:

Mask your password with this VBA inputbox

How to hide the password text in a textbox control in Excel – VBA

 

2 thoughts on “Automatically Display Worksheets of Specific User

  1. Barry Fitzpatrick

    I think the worksheets should be hidden before any save, in case the workbook is opened with macros disabled

    Reply
  2. Barry Fitzpatrick

    There could also be a problem if a User were to rename a worksheet tab. It would be better to use the worksheets code name.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *