CODEWITHSUNDEEP
vbaexcelworksheet
Elena
Elena

Reputation: 77

how to allow only specific users to unhide a worksheet

I already have a macro below that un-hides a worksheet at the click of a button and works okay. However I want this macro to be changed so that ONLY two users (whose usernames are "JSMITH" AND "DTAYLOR") are able to unhide this sheet called "Rates".

If someone else (whose username is not one of the two mentioned above) tries to unhide the sheet, I want Excel to display a message "you're not authorised to open this".

Moreover, I need to make sure that only those two users are able to un-hide in a traditional way without vba (eg by right-clicking on a visible worksheet tab and choose Unhide or from any worksheet tab, choose Format, Sheet, and then Unhide).

Could you please advise how to modify the following code to do the all the things described above?

I came up with this but it doesn't work: 

Sub GoToRates_WS() 

Select Case Environ$("username") 

Case "jsmith", "taylor" 

Worksheets("Rates").Visible = True 
ThisWorkbook.Sheets("Rates").Activate 
Case Else MsgBox "you're not authorised to open this" 
End Select 

End Sub

Upvotes: 2

Views: 1144

Answers (1)

A.S.H
A.S.H

Reputation: 29332

1- Open your ThisWorkbook code Module.

2- Paste this line at the top of it:

Private RatesVisible As Variant

3- find the following routine: 

Private Sub Workbook_Open()
...
...
End Sub

Insert the following line just before the line End Sub:

RatesVisible = Worksheets("Rates").Visible

4- Delete your old routine GoToRates_WS

5- Copy the following code and paste it at the end of the code module:

Private Function privilegedUser() As Boolean
    Select Case UCase(Environ$("username"))
        Case "JSMITH", "DTAYLOR"
            privilegedUser = True
        Case Else
        End Select
End Function

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    RatesVisible = Worksheets("Rates").Visible
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Sh.Name <> "Rates" Then
        RatesVisible = Worksheets("Rates").Visible
        Exit Sub
    End If
    If privilegedUser Then
        RatesVisible = Worksheets("Rates").Visible
    Else
        Worksheets("Rates").Visible = RatesVisible
    End If
End Sub

Private Sub GoToRates_WS()
    If privilegedUser Then
        RatesVisible = xlSheetVisible
        Worksheets("Rates").Visible = xlSheetVisible
    Else
        MsgBox "You are not authorized to open this worksheet"
    End If
End Sub

Upvotes: 3

Related Questions