nlroberts3
New Member
- Joined
- Feb 20, 2019
- Messages
- 3
I'm managing a workbook that includes payroll information, which contains confidential information like hourly wages. Each payperiod I reset the workbook and advance the calendar dates so my assistant can enter in the hours worked for each employee. Each employee has their own worksheet. All the wages and totals are on a separate worksheet at the end of the workbook called "Totals". To secure the worksheet so it cannot be viewed by my assistant I hide the worksheet and protect the workbook to prevent the structure from being changed. This way my assistant cannot "unhide" the "Totals" worksheet and see the hourly wages.
At this point I thought everything was grand, but today I opened the payroll workbook (while protected) and opened up the VBA IDE and noticed that I was able to see a list of all the worksheets in the "tree" displaying all the workbook objects, including my hidden "Totals" worksheet.
From here I know the file name and the worksheet name. This is a problem. I could open up a blank workbook (or use an existing worksheet***) and then in cell A1 of that new sheet I can reference cell A1 of my confidential worksheet.... ='[Payroll Workbook.xlsx]Totals'!A1
Then I can copy and paste that formula through the blank worksheet.
At this point I have effectively duplicated the confidential data from a hidden worksheet and a protected workbook to a new workbook. I can clearly see column headers with Employee names and hourly wages.
Let me know if I can define my problem more clearly. This is my first time ever posting something on a forum because I can normally figure it out or find it already answered, but this has me stumped
Thank you!
*** I can somewhat prevent this by restricting editing to only certain cells needed to enter employee hours which limits the window of data someone can view at one time, but to go even further I can then use data validation to prevent the use of formulas in in the cells. I'm not sure I can protect a sheet to prevent changing data validation while also allowing cells to be edited, though.
At this point I thought everything was grand, but today I opened the payroll workbook (while protected) and opened up the VBA IDE and noticed that I was able to see a list of all the worksheets in the "tree" displaying all the workbook objects, including my hidden "Totals" worksheet.
From here I know the file name and the worksheet name. This is a problem. I could open up a blank workbook (or use an existing worksheet***) and then in cell A1 of that new sheet I can reference cell A1 of my confidential worksheet.... ='[Payroll Workbook.xlsx]Totals'!A1
Then I can copy and paste that formula through the blank worksheet.
At this point I have effectively duplicated the confidential data from a hidden worksheet and a protected workbook to a new workbook. I can clearly see column headers with Employee names and hourly wages.
Let me know if I can define my problem more clearly. This is my first time ever posting something on a forum because I can normally figure it out or find it already answered, but this has me stumped
Thank you!
*** I can somewhat prevent this by restricting editing to only certain cells needed to enter employee hours which limits the window of data someone can view at one time, but to go even further I can then use data validation to prevent the use of formulas in in the cells. I'm not sure I can protect a sheet to prevent changing data validation while also allowing cells to be edited, though.
