VBA Code Help!!

M

Maebus

New Member
Joined
Jun 25, 2024
Messages
20
Office Version
  1. 365
Platform
  1. Windows
I am creating a workbook to display employee performance stats with a separate sheet for each supervisor. I want it to work that each supervisor can only view their individual sheet and all others are very hidden so they cannot view other supervisors employees. I have code but when I have people test they are still able to view and select all worksheets. What am I missing or doing wrong? Here is what I have. Thanks in advance for any help.

Private Sub Workbook_Open()
Dim currentUser As String

' Get the current user
currentUser = Environ("USERNAME")

' Allow access to specific worksheets for specific users
Select Case currentUser
Case "JR", "A"
' Allow access to the worksheet named "A" for specific users
Sheets("A").Visible = xlSheetVisible
' Hide specific worksheets very hidden for selected users
Sheets("B").Visible = xlSheetVeryHidden
Sheets("C").Visible = xlSheetVeryHidden
Sheets("D").Visible = xlSheetVeryHidden
Sheets("I").Visible = xlSheetVeryHidden
Sheets("R").Visible = xlSheetVeryHidden
Sheets("S").Visible = xlSheetVeryHidden
Sheets("M").Visible = xlSheetVeryHidden
Case "B", "JR"
' Allow access to the worksheet named "B" for a specific user
Sheets("B").Visible = xlSheetVisible
' Hide specific worksheets very hidden for selected users
Sheets("A").Visible = xlSheetVeryHidden
Sheets("C").Visible = xlSheetVeryHidden
Sheets("D").Visible = xlSheetVeryHidden
Sheets("I").Visible = xlSheetVeryHidden
Sheets("R").Visible = xlSheetVeryHidden
Sheets("S").Visible = xlSheetVeryHidden
Sheets("M").Visible = xlSheetVeryHidden
Case "D", "JR"
' Allow access to the worksheet named "D" for a specific user
Sheets("D").Visible = xlSheetVisible
' Hide specific worksheets very hidden for selected users
Sheets("A").Visible = xlSheetVeryHidden
Sheets("B").Visible = xlSheetVeryHidden
Sheets("C").Visible = xlSheetVeryHidden
Sheets("I").Visible = xlSheetVeryHidden
Sheets("R").Visible = xlSheetVeryHidden
Sheets("S").Visible = xlSheetVeryHidden
Sheets("M").Visible = xlSheetVeryHidden
Case "I", "JR"
' Allow access to the worksheet named "I" for a specific user
Sheets("I").Visible = xlSheetVisible
' Hide specific worksheets very hidden for selected users
Sheets("A").Visible = xlSheetVeryHidden
Sheets("B").Visible = xlSheetVeryHidden
Sheets("C").Visible = xlSheetVeryHidden
Sheets("D").Visible = xlSheetVeryHidden
Sheets("R").Visible = xlSheetVeryHidden
Sheets("S").Visible = xlSheetVeryHidden
Sheets("M").Visible = xlSheetVeryHidden
Case "R", "JR"
' Allow access to the worksheet named "R" for a specific user
Sheets("R").Visible = xlSheetVisible
' Hide specific worksheets very hidden for selected users
Sheets("A").Visible = xlSheetVeryHidden
Sheets("B").Visible = xlSheetVeryHidden
Sheets("C").Visible = xlSheetVeryHidden
Sheets("D").Visible = xlSheetVeryHidden
Sheets("I").Visible = xlSheetVeryHidden
Sheets("S").Visible = xlSheetVeryHidden
Sheets("M").Visible = xlSheetVeryHidden
Case "S", "JR"
' Allow access to the worksheet named "S" for a specific user
Sheets("S").Visible = xlSheetVisible
' Hide specific worksheets very hidden for selected users
Sheets("A").Visible = xlSheetVeryHidden
Sheets("B").Visible = xlSheetVeryHidden
Sheets("C").Visible = xlSheetVeryHidden
Sheets("D").Visible = xlSheetVeryHidden
Sheets("I").Visible = xlSheetVeryHidden
Sheets("R").Visible = xlSheetVeryHidden
Sheets("M").Visible = xlSheetVeryHidden
Case "M", "JR"
' Allow access to the worksheet named "M" for a specific user
Sheets("M").Visible = xlSheetVisible
' Hide specific worksheets very hidden for selected users
Sheets("A").Visible = xlSheetVeryHidden
Sheets("B").Visible = xlSheetVeryHidden
Sheets("C").Visible = xlSheetVeryHidden
Sheets("D").Visible = xlSheetVeryHidden
Sheets("I").Visible = xlSheetVeryHidden
Sheets("R").Visible = xlSheetVeryHidden
Sheets("S").Visible = xlSheetVeryHidden
End Select
End Sub
 
Sort by date Sort by votes
What if you change the code to this?
VBA Code: 
Private Sub Workbook_Open()
    Dim currentUser As String
    Dim allowedSheet As String
    Dim ws As Worksheet

    ' Get the current user
    currentUser = Environ("USERNAME")

    ' Check if current user is "JR"
    If currentUser = "JR" Then
        ' "JR" can access all sheets, make all sheets visible and exit sub
        For Each ws In ThisWorkbook.Worksheets
            ws.Visible = xlSheetVisible
        Next ws
        Exit Sub
    Else
        ' Define allowed sheet based on user
        allowedSheet = currentUser
    End If

    ' Loop through all sheets and set visibility
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = allowedSheet Then
            ws.Visible = xlSheetVisible
        Else
            ws.Visible = xlSheetVeryHidden
        End If
    Next ws
End Sub
 
Upvote 0

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Cubist said:
What if you change the code to this?
VBA Code: 
Private Sub Workbook_Open()
    Dim currentUser As String
    Dim allowedSheet As String
    Dim ws As Worksheet

    ' Get the current user
    currentUser = Environ("USERNAME")

    ' Check if current user is "JR"
    If currentUser = "JR" Then
        ' "JR" can access all sheets, make all sheets visible and exit sub
        For Each ws In ThisWorkbook.Worksheets
            ws.Visible = xlSheetVisible
        Next ws
        Exit Sub
    Else
        ' Define allowed sheet based on user
        allowedSheet = currentUser
    End If

    ' Loop through all sheets and set visibility
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = allowedSheet Then
            ws.Visible = xlSheetVisible
        Else
            ws.Visible = xlSheetVeryHidden
        End If
    Next ws
End Sub
Click to expand...
Same error same line
 
Upvote 0
It's most likely because
1) no sheet name matches the user name. In the immediate window, type Debug.Print Environ("USERNAME") and hit enter. This should show you the user name. Make sure that there's a sheet that matches the user name. Often it has a period separating the first and last name so your sheet name must be as well.

2) When the names do not match, it'll try to hide all of the sheets, however, there must be at least one sheet visible. You can't hide all of the sheets.
 
Upvote 0
Cubist said:
It's most likely because
1) no sheet name matches the user name. In the immediate window, type Debug.Print Environ("USERNAME") and hit enter. This should show you the user name. Make sure that there's a sheet that matches the user name. Often it has a period separating the first and last name so your sheet name must be as well.

2) When the names do not match, it'll try to hide all of the sheets, however, there must be at least one sheet visible. You can't hide all of the sheets.
Click to expand...
I’m confused as to where I should be typing this. Also, as of right now, I’m the only user accessing it.
 
Upvote 0
Cubist said:
It's most likely because
1) no sheet name matches the user name. In the immediate window, type Debug.Print Environ("USERNAME") and hit enter. This should show you the user name. Make sure that there's a sheet that matches the user name. Often it has a period separating the first and last name so your sheet name must be as well.

2) When the names do not match, it'll try to hide all of the sheets, however, there must be at least one sheet visible. You can't hide all of the sheets.
Click to expand...
Also the sheets are as follows:
HomePage
Alan
Beth
Charlie
Danny
Ira
Randy
Simon
Matt
 
Upvote 0
The Immediate Window is located below the code editor. Sometimes it's minimized, you'll have to make it bigger or it's it's not visible at all try Ctrl + G.
As I mentioned the Environ("USERNAME") would return something like "alan.ross", thus it's looking for a sheet named "alan.ross", but if your sheet name is "Alan" it will throw an error. The returned string from Environ("USERNAME") must match exactly with the sheet name.
1719412483459.png
 
Upvote 0
Cubist said:
The Immediate Window is located below the code editor. Sometimes it's minimized, you'll have to make it bigger or it's it's not visible at all try Ctrl + G.
As I mentioned the Environ("USERNAME") would return something like "alan.ross", thus it's looking for a sheet named "alan.ross", but if your sheet name is "Alan" it will throw an error. The returned string from Environ("USERNAME") must match exactly with the sheet name.
View attachment 113272
Click to expand...
So it returned my username but I won’t have a worksheet named for me as I’m the super user of the entire document
 
Upvote 0
Did you change this part to your user name?
VBA Code: 
Private Sub Workbook_Open()
...
    If currentUser = "JR" Then '<- Change "JR" to your name
        For Each ws In ThisWorkbook.Worksheets
            ws.Visible = xlSheetVisible
        Next ws
        Exit Sub
....
 
Upvote 0
Cubist said:
The Immediate Window is located below the code editor. Sometimes it's minimized, you'll have to make it bigger or it's it's not visible at all try Ctrl + G.
As I mentioned the Environ("USERNAME") would return something like "alan.ross", thus it's looking for a sheet named "alan.ross", but if your sheet name is "Alan" it will throw an error. The returned string from Environ("USERNAME") must match exactly with the sheet name.
View attachment 113272
Click to expand...
And I want each user to be able to see the HomePage and their individual sheet.
 
Upvote 0
Cubist said:
Did you change this part to your user name?
VBA Code: 
Private Sub Workbook_Open()
...
    If currentUser = "JR" Then '<- Change "JR" to your name
        For Each ws In ThisWorkbook.Worksheets
            ws.Visible = xlSheetVisible
        Next ws
        Exit Sub
....
Click to expand...
I just did. Reopened and no error.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Worksheet Change - multiple Loops in one sub
2
Replies
15
Views
725
mumps
mumps
C
  • Question
username access to different worksheet VBA
Replies
4
Views
183
cilantro00
C
K0st4din
  • Solved
Macro help, after pasting with Call option...... stopped working and gives error
Replies
2
Views
369
K0st4din
K0st4din
L
  • Question
VBA code not working on Mac
Replies
7
Views
978
Jim Gordon
J
B
  • Solved
Combining vba
Replies
1
Views
477
Joe4
Joe4

Forum statistics

Threads
1,220,965
Messages
6,157,119
Members
451,398
Latest member
rjsteward

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top