Limiting which sheets users can unhide

[Laughattack1834]

For my company, we have Excel pulling from our database to generate reports and it includes some sensitive information (Salary, PII). I want to make it so that when a person opens the shared Excel sheet they hit a button, it checks the username, and unhides sheet2 and sheet3, or only unhide sheet3 etc.

I have the whole setup created with the buttons and usernames but if I protect the sheet then nobody can unhide the sheet they need. It either has no security where anyone can unhide a sheet, or too much security that no one can unhide any sheet.

How can I not allow users to unhide a sheet, while using a macro button to unhide the sheets using their username permissions?

Sub Salary_Unlock()
    Dim i As Long
    For i = 1 To 10
        If (Cells(i, 2) = Application.UserName) Then
            Sheet8.Visible = xlSheetVisible
            Exit For
        Else
            Sheet8.Visible = xlSheetVeryHidden
        End If
    Next i
End Sub

 

[dmt32]

Hi,
Just a my view but I would not include any personal data you do not want shared in a workbook – excel security is quite weak & competent users can break quite quickly.

If you are extracting data from your corporate database you could consider couple of approaches.

1 – Filter out the sensitive data in to another workbook & share that

2 – Create workbook templates for each user & have these import data relevant to them only. (some VBA likely to be needed)
These could read directly from your database or, you can create another workbook as the database.

Others here may have alternative suggestions.

Dave

 

[RedBeard]

You should solve this at the database level, make the report you extract based on the username there instead and you won't have to rely on the nonexistent security Excel provides.
You'll also reduce the likelihood of privacy breaches if you're dealing with PII data sets.

 

[Logit]

.
I agree with the others that you should not include sensitive information in the same workbook that unauthorized users have access to. No software is immune from
error and Excel is known to be easy to circumvent. Placing the salary info into another workbook that only authorized personnel have access to / know about would be
a good security measure. Then adding another layer of security with a macro - something like this one gives you one more 'door' to go thru:

Paste into a UserForm:

Option Explicit


Private Sub CommandButton2_Click()
    Unload Me
End Sub


  
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = 0 Then Cancel = True
End Sub


Private Sub CommandButton1_Click()
Dim username, password As String
username = TextBox1.Text
password = TextBox2.Text
Application.ScreenUpdating = False


If username = "user" And password = "pw" Then
    MsgBox "You entered the correct info ", vbInformation, "Log In Correct"
    Unload Me
    Sheets("Sheet2").Visible = xlSheetVisible
    Sheets("Sheet3").Visible = xlSheetVisible
    Exit Sub
ElseIf username = "" And password = "" Then
    MsgBox "You entered the WRONG info ", vbCritical, "Log In Incorrect"
    Unload Me
    Application.DisplayAlerts = False
    Application.Visible = False
    ThisWorkbook.Saved = True
    Application.Quit
    Exit Sub
Else
    MsgBox "You entered the WRONG info ", vbCritical, "Log In Incorrect"
    Unload Me
    Application.DisplayAlerts = False
    Application.Visible = False
    ThisWorkbook.Saved = True
    Application.Quit
    Exit Sub
End If
Application.ScreenUpdating = True
End Sub

Sample workbook download link: https://www.amazon.com/clouddrive/s...HvOyDIoGQ8Ljp79kap?ref_=cd_ph_share_link_copy