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

 

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

2 thoughts on “Automatically Display Worksheets of Specific User”

  1. 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.

Leave a Reply

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