1

I'm having a Workbook in which all sheets are hidden except a Base sheet "CRM_Competencies_Dashboard"

As soon as the user logs in I will pull the user id with Environ("Username") function. Then I will store that value in a string variable "User_Name"

Now my goal is to Un-hide only that sheet.

And there are a list of users who will access this workbook. Every-time they open only their respective sheet should be visible

I tried to Un-hide the sheet by using the below function but I didn't get a way to pass the sheet name as a variable as shown below

Sub Display_User_Specific_data()
Dim User_Name As String
User_Name = Environ("Username")
ActiveWorkbook.Sheets(User_Name).Select
ActiveSheet.Visible = xlSheetVisible
End Sub

Sheets(User_Name) should become un-hidden. And the value of variable User_Name can change for different users.

Mikku
  • 6,538
  • 3
  • 15
  • 38
Abhishek Singh
  • 69
  • 1
  • 8
  • You cannot select an invisible sheet. BTW you should avoid using Select at all. – Vincent G Aug 28 '19 at 07:52
  • 2
    Note that username can be up to [104 characters](https://learn.microsoft.com/en-us/previous-versions/windows/it-pro/windows-2000-server/bb726984(v=technet.10)) but sheets names are limited to [31 characters.](https://stackoverflow.com/questions/3681868/is-there-a-limit-on-an-excel-worksheets-name-length) And I'm not sure if the allowed chars are the same for the two. – Vincent G Aug 28 '19 at 08:04

1 Answers1

3

You cannot select a sheet that is Hidden. So you have to un-hide it First.

Use this:

Sub Display_User_Specific_data()
    Dim User_Name As String
    User_Name = Environ("Username")
    ActiveWorkbook.Sheets(User_Name).Visible = xlSheetVisible
End Sub
  • Also, Keep in mind that the user name will be of the form mohit.bansal if the name is Mohit Bansal , so your sheet name should be mohit.bansal

Demo:

enter image description here

Addition by @Pulitian

  • To add to this, add the following to your 'this workbook' object to hide the sheet upon workbook close. Otherwise it will still be open when the next person uses the workbook.
Private Sub Workbook_BeforeClose(Cancel As Boolean) 
    ActiveWorkbook.Sheets(Environ("Username")).Visible = xlSheetVeryHidden 
End Sub
Mikku
  • 6,538
  • 3
  • 15
  • 38
  • 1
    To add to this, add the following to your 'this workbook' object to hide the sheet upon workbook close. Otherwise it will still be open when the next person uses the workbook. ```Private Sub Workbook_BeforeClose(Cancel As Boolean) ActiveWorkbook.Sheets(Environ("Username")).Visible = xlSheetVeryHidden End Sub``` – Plutian Aug 28 '19 at 08:25