Encrypting and securing sheets for users

Question

One sheet needs to be locked so that only a select group of employees may see it. I did that by using the macro listed below, which starts up when you open the workbook. To check if the individuals who opened the sheet are on the list, it merely produces a "check sheet" and executes an if statement.

It functions as intended, but there is a problem—once macros are disabled, the specified sheet is no longer extremely secret, giving everyone access to it.

Is there a better way to do it?

Private Sub Workbook_Open()

Application.ScreenUpdating = False

Sheets("OTE ratios").Visible = xlVeryHidden

Dim IFFORMULA As String
IFFORMULA = "=IF(OR(AA1=A2,AA2=AA1, AA3=AA1,AA4=AA1,AA5=AA1,AA6=AA1,AA7=AA1,AA8=AA1),""Yes"", ""No"")"

Sheets.Add.Name = "check"

Worksheets("check").Range("A2").Value = Worksheets("January").Range("A2").Value

Worksheets("check").Range("AB1").Value = IFFORMULA
Worksheets("check").Range("AA1").Value = Application.UserName
Worksheets("check").Range("AA2").Value = "USER1"
Worksheets("check").Range("AA3").Value = " USER2"
Worksheets("check").Range("AA4").Value = " USER3"
Worksheets("check").Range("AA5").Value = " USER4"
Worksheets("check").Range("AA6").Value = " USER5"

'DM goes here
Worksheets("check").Range("AA8").Value = " USER6"

Worksheets("check").Calculate

''''IF statement
If Worksheets("check").Range("AB1").Value = "Yes" Then
Sheets("OTE ratios").Visible = xlSheetVisible
End If

''''remove the sheet
    Application.DisplayAlerts = False
Worksheets("check").Delete
    Application.DisplayAlerts = True


Application.ScreenUpdating = True

Worksheets("January").Activate

End Sub

Kithuzzz · Answer 1 · Mar 21, 2023

First off, instead of using a formula in a cell to perform the If check, why not do it with VBA code if you're going to write the users in VBA code?

Second, I believe your issue is that you use xlVeryHidden to completely conceal the existing sheet before generating the new one, but Excel must always maintain at least one sheet visible. Rather, you ought to make the new sheet first, then conceal the old one.

Instead of producing the blank sheet on open and the other sheet hidden by default, you could keep the blank sheet visible by default and choose whether to reveal the hidden sheet or not.