Encrypting and securing sheets for users

0 votes

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
Mar 21, 2023 in Others by narikkadan
• 63,420 points
272 views

1 answer to this question.

0 votes
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.
answered Mar 21, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer

Creating sheets with names in column B and assigning data to each sheet for particular name

after the first occurrence of Set sht = ...READ MORE

answered Feb 13, 2023 in Others by narikkadan
• 63,420 points
503 views
0 votes
2 answers
0 votes
1 answer

AngularJS SEO - Once and for all

java script cant be or wont be ...READ MORE

answered Feb 14, 2022 in Others by narikkadan
• 63,420 points
357 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
905 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,224 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
515 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
757 views
0 votes
1 answer

How to query a matrix for multiple values and receive value

Try: =IFERROR(INDEX(SORT(SORT(FILTER(MATRIX, (LENGTHS>length)*(WEIGHTS>weight)),1,1),2,1),1,3),1) , where MATRIX, LENGTHS, and WEIGHTS ...READ MORE

answered Feb 11, 2023 in Others by Kithuzzz
• 38,010 points
327 views
0 votes
1 answer

Excel VBA: Obtain the Column and Row of current element in "For Each" loop

Try this: MsgBox rng.Address(RowAbsolute:=False, ColumnAbsolute:=F ...READ MORE

answered Feb 14, 2023 in Others by Kithuzzz
• 38,010 points
775 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP