VBA Code Help!!

M

Maebus

New Member
Joined
Jun 25, 2024
Messages
24
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
First of all, before a user opens the workbook, all user sheets should be very hidden. Only until the macros have run to verify a user, should a sheet be shown.

You can hide every user sheet when a user closes the workbook.

Are those really user names (Login name)? A single letter login name is unusual
 
Upvote 0
Jeffrey Mahoney said:
First of all, before a user opens the workbook, all user sheets should be very hidden. Only until the macros have run to verify a user, should a sheet be shown.

You can hide every user sheet when a user closes the workbook.

Are those really user names (Login name)? A single letter login name is unusual
Click to expand...
How would you change it? I’m new to vba coding and tried to do it through examples I’ve seen on the web.
No they are not. The letters are place holders for names for privacy.
 
Upvote 0
I think you can shorten your code to this. (Not tested).
VBA Code: 
Private Sub Workbook_Open()
    Dim currentUser As String
    Dim allowedSheet As String
    Dim hiddenSheets() 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
        allowedSheet = ""
    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 allowedSheet = "" Or ws.Name = allowedSheet Then
            ws.Visible = xlSheetVisible
        Else
            ws.Visible = xlSheetVeryHidden
        End If
    Next ws
End Sub
 
Upvote 0
Cubist said:
I think you can shorten your code to this. (Not tested).
VBA Code: 
Private Sub Workbook_Open()
    Dim currentUser As String
    Dim allowedSheet As String
    Dim hiddenSheets() 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
        allowedSheet = ""
    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 allowedSheet = "" Or ws.Name = allowedSheet Then
            ws.Visible = xlSheetVisible
        Else
            ws.Visible = xlSheetVeryHidden
        End If
    Next ws
End Sub
Click to expand...
This looks great. I’ll test this tomorrow. Where in this code would I put the other users and the sheets they can access? This is my problem, I never know where certain things should go.
 
Upvote 0
It's currently showing sheet names that match the user name. What other sheets do you want to give access to?
 
Upvote 0
Cubist said:
It's currently showing sheet names that match the user name. What other sheets do you want to give access to?
Click to expand...
The list of names is a single letter for privacy reasons. The users are as follows:
JR - already there (is a super user and should access all sheets.
A - access sheet A only
B - access sheet B only
C - access sheet C only
D - access sheet D only
I - access sheet I only
S - access sheet S only
M - access sheet M only

I can update the actual usernames and sheet names (as they are the users names) where applicable.
 
Upvote 0
I must be misunderstanding your question (maybe). This part of the code loops through every sheet in the workbook and checks whether the Environment user name is the same as the sheet name. If yes, then make it visible, else hide it. For example, if the user name is "Adrian", it'll show a sheet that is named "Adrian".
VBA Code: 
    ' Loop through all sheets and set visibility
    For Each ws In ThisWorkbook.Worksheets
        If allowedSheet = "" Or ws.Name = allowedSheet Then
            ws.Visible = xlSheetVisible
        Else
            ws.Visible = xlSheetVeryHidden
        End If
    Next ws


Here I'm setting JR to "", giving him all of the access to all sheets.
VBA Code: 
    ' Check if current user is "JR"
    If currentUser = "JR" Then
        ' "JR" can access all sheets
        allowedSheet = ""
    Else
        ' Define allowed sheet based on user
        allowedSheet = currentUser
    End If
 
Upvote 0
Cubist said:
I must be misunderstanding your question (maybe). This part of the code loops through every sheet in the workbook and checks whether the Environment user name is the same as the sheet name. If yes, then make it visible, else hide it. For example, if the user name is "Adrian", it'll show a sheet that is named "Adrian".
VBA Code: 
    ' Loop through all sheets and set visibility
    For Each ws In ThisWorkbook.Worksheets
        If allowedSheet = "" Or ws.Name = allowedSheet Then
            ws.Visible = xlSheetVisible
        Else
            ws.Visible = xlSheetVeryHidden
        End If
    Next ws


Here I'm setting JR to "", giving him all of the access to all sheets.
VBA Code: 
    ' Check if current user is "JR"
    If currentUser = "JR" Then
        ' "JR" can access all sheets
        allowedSheet = ""
    Else
        ' Define allowed sheet based on user
        allowedSheet = currentUser
    End If
Click to expand...
Ok got it. Thank you. I’ll test it out. Thank you for explaining it to me.
 
Upvote 0
Cubist said:
I must be misunderstanding your question (maybe). This part of the code loops through every sheet in the workbook and checks whether the Environment user name is the same as the sheet name. If yes, then make it visible, else hide it. For example, if the user name is "Adrian", it'll show a sheet that is named "Adrian".
VBA Code: 
    ' Loop through all sheets and set visibility
    For Each ws In ThisWorkbook.Worksheets
        If allowedSheet = "" Or ws.Name = allowedSheet Then
            ws.Visible = xlSheetVisible
        Else
            ws.Visible = xlSheetVeryHidden
        End If
    Next ws


Here I'm setting JR to "", giving him all of the access to all sheets.
VBA Code: 
    ' Check if current user is "JR"
    If currentUser = "JR" Then
        ' "JR" can access all sheets
        allowedSheet = ""
    Else
        ' Define allowed sheet based on user
        allowedSheet = currentUser
    End If
Click to expand...
I tested the code and updated the super user name but received an error upon opening:
Run-time error ‘1004’:
Method ‘Visible’ of object’_Worksheet’ failed

When I click on debug it highlights the line:
ws.Visible = xlSheetVeryHidden
 
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
812
mumps
mumps
C
  • Question Question
username access to different worksheet VBA
Replies
4
Views
285
cilantro00
C
K0st4din
  • Solved
Macro help, after pasting with Call option...... stopped working and gives error
Replies
2
Views
402
K0st4din
K0st4din
L
  • Question Question
VBA code not working on Mac
Replies
7
Views
1K
Jim Gordon
J
S
  • Solved
VBA code to use checkboxes to hide sheets.
2
Replies
14
Views
694
Joe4
Joe4

Forum statistics

Threads
1,223,884
Messages
6,175,175
Members
452,615
Latest member
bogeys2birdies

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