Password protecting a checkbox

[BlissC]

As, due to the COVID-19 pandemic, the majority of my employer's employees are working from home, and with many employees, their leave cards for requesting annual leave are locked in their locker in the office, I've had to develop an electronic leave card so employees can still book leave. It's practically working just as I want, and we've been trialling it with my team for a few weeks. More teams now are wanting to use the form, and the only only issue, which thus far I've been ignoring, is that the page where the employee's manager authorises their leave is visible. With other teams coming on board now though, I want to try and nail the issue before they start using the forms now while I've only got 15 to update, rather than in a few weeks when I'd have to update many more!

The workbook has 5 worksheets: Leave, Configuration, Authorisation, List configuration, Changelog.

Employees have access to 'Leave' and 'Configuration'. At the bottom of the Leave sheet (out of the way where they're not likely to notice it), which is the sheet on which employees use to book leave there is a checkbox which hides/unhides the remaining three sheets. I didn't want to protect the sheets using Excel's built-in protection as it would rely on managers 'unlocking' the sheet to authorise leave and then remembering to re-protect the page(s) with the password after they'd finished. When a manager wants to authorise an employee's leave, they check the checkbox, and the Authorisation, List configuration, and Changelog pages unhide. Before they save and close the worksheet, they have to remember to un-check the checkbox.

Ideally what I need to do is to password protect the checkbox so that a password is needed to be able to use the checkbox, and then un-check the checkbox when the file is closed. I have a small piece of VBA, Module 1, which works the hide/unhide with the checkbox. There is a range on the Leave worksheet that gives the names of the worksheets. There is no other VBA in the workbook.

So, what I'm wanting the VBA to do is:

• Request a password if the checkbox is clicked
• If the correct password is given, 'unlock' the checkbox so it can be checked
• If the correct password isn't given, deny access to the checkbox and allow the user to try again
• When the user closes to workbook, uncheck the checkbox, and re-lock the checkbox

Is this do-able? (My VBA skills are at the level of follow instructions and copy and paste VBA where I'm told to, and change names etc. in the code. When I see it I can understand what it's doing, but I don't understand it well enough to write it myself)

This is the existing VBA that I'm using:

Sub ShowHideWorksheets()
Dim Cell As Range
For Each Cell In Range("Q26:Q28")
    ActiveWorkbook.Worksheets(Cell.Value).Visible = Not ActiveWorkbook.Worksheets(Cell.Value).Visible
Next Cell
End Sub

Is anyone able to help out at all?

Thanks, Bliss

 

[Eric W]

You could possibly do away with the password altogether. Look at the Application.UserName method.

Sub ShowHideWorksheets()
Dim Cell As Range, ManagerNames As Variant

ManagerNames = Array("Mary Smith", "Max Power", "Ed Ames")
If UBound(Filter(ManagerNames, Application.UserName)) < 0 Then Exit Sub

For Each Cell In Range("Q26:Q28")
    ActiveWorkbook.Worksheets(Cell.Value).Visible = Not ActiveWorkbook.Worksheets(Cell.Value).Visible
Next Cell
End Sub

In your code, just list the user names of the managers. They'll be able to use the checkbox, no one else will. Let me know if you still want the password version though.

If you want to get a little fancier, you can get rid of the checkbox. Set up an AutoOpen event which unhides the sheets if a manager opens the workbook. Then add a BeforeSave event that hides the sheets.

BUT! Keep in mind that security on an Excel workbook is pretty weak. Someone with only moderate skills can get past Excel's built-in protection.

 

[BlissC]

Hi Eric,

Many thanks for taking the time to look at this. I've tried swapping out my original code with yours, with the result that nothing happens except that when the workbook is opened there's already a tick in the checkbox and all of the sheets are visible. If you then un-check the box and re-check it, then the sheets are hidden. I've tried putting the managers' names in the array, including my own, but that doesn't have any effect. As well as the manager's name in Firstname Surname format, I've also tried using the system username, which is in the format SurnameFirstInitial, so for example 'Bob Smith' would be 'smithb'. That made no difference either.

This is what I've now got as Module 1:

Sub ShowHideWorksheets()
Dim Cell As Range, ManagerNames As Variant

ManagerNames = Array("Andy Green", "Cathy Jones", "Paul Smith", "Vanessa Brown", "turnerr")
If UBound(Filter(ManagerNames, Application.UserName)) < 0 Then Exit Sub

For Each Cell In Range("Q26:Q28")
    ActiveWorkbook.Worksheets(Cell.Value).Visible = Not ActiveWorkbook.Worksheets(Cell.Value).Visible
Next Cell
End Sub

I've left my own name in the system username format, but I've tried with both versions. As I said in my initial post, I'm a novice when it comes to VBA, so I've no idea what I'm doing wrong.

Am I misunderstanding what I need to do with the manager's names? I'd be grateful if you could advise me where I'm going wrong with this.

Many thanks,

Bliss

 

[BlissC]

Hi Eric,

Me again! Sorry, ignore my last post - I realised I was trying it out on a copy that didn't have my name on the manager list, so of course it wasn't working for me! (I'd be dangerous if I had a brain! ?)

It's working perfectly now. Thank you so much for your help!

 

[BlissC]

This is working perfectly now, except that the manager has to make sure they put a check in the checkbox before they save and close the file, otherwise, if they leave the checkbox un-checked, when the employee next opens the file all of the sheets will be visible to them.

Is there a way of ensuring the checkbox is checked before saving and closing the file?

 

[Eric W]

This is what I mentioned about getting rid of the checkbox. If you want to keep the checkbox, go to the VBA editor, double click on the ThisWorkbook sheet in the left navigation pane, and enter this code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim cell As Range

    For Each cell In Sheets("Sheet1").Range("Q26:Q28")
        ActiveWorkbook.Worksheets(cell.Value).Visible = xlHidden
    Next cell
    
    Sheets("Sheet1").CheckBoxes("Check Box 1").Value = True
End Sub

I'm assuming that your check box is a form control. This will run any time someone tries to save the workbook. What might be even easier though, is to get rid of the checkbox entirely. Try entering these 2 macros on the ThisWorkbook sheet (in place of the last one):

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim cell As Range

    For Each cell In Sheets("Sheet1").Range("Q26:Q28")
        ActiveWorkbook.Worksheets(cell.Value).Visible = xlVeryHidden
    Next cell
    
End Sub

Private Sub Workbook_Open()
Dim cell As Range, ManagerNames As Variant

    ManagerNames = Array("John Doe", "Mary Roe")
    If UBound(Filter(ManagerNames, Application.UserName)) < 0 Then Exit Sub
    
    For Each cell In Sheets("Sheet1").Range("Q26:Q28")
        ActiveWorkbook.Worksheets(cell.Value).Visible = True
    Next cell
    
End Sub

The BeforeSave is almost the same, except I removed the checkbox line, and I switched the parameter to xlVeryHidden. I did that because if a sheet is just hidden, all someone has to do to see the sheet is right click on the visible sheet tab, select Unhide, and they can see it all.

The Workbook_Open macro will run automatically when the workbook is opened. If the user's username is on the list, the hidden sheets will open automatically. For anyone else, they'll never see them.

There are still ways to defeat this, but at least now they'd have to know a bit more about VBA, and not just click Unhide.

 

[BlissC]

Hi Eric,

Thank you so much for posting the alternative codes. Having been distracted by other stuff at work, I have to admit I haven't checked back here in a while, but popped in to ask if you could share the code for getting rid of the check box. My bad. I should have checked back here sooner!

I'll try that in a while once I've had dinner.

Thank you again!

Bliss

 

[BlissC]

Hi Eric,

I wonder if I could impose on you again with a little more help with this issue. The last two macros you posted (the ones with the checkbox line removed) is working beautifully. The managers using the leave card with their staff much prefer the latest version, as they don't have to remember to tick/untick the box. I'm expanding the leave card's use to more employees now, but before I can roll it out more widely though, I need to fix a couple of issues. As it's going to be used much more widely I and our admin team need to be able to update things easily, without having to go into the leave cards individually, so I've set up another workbook that the leave card grabs data from, for example the list of authorised managers able to access the hidden pages.

While the macro's working fine for hiding the authorisation page etc., the 'Leave' and 'Configuration' pages currently are visible to anyone, so I'm trying to restrict access to those two pages too, so that it's visible only to the employee whose leave card it is, and the authorised managers who have access to the authorisation page etc. Access to those two sheets is authorised managers plus the employee. I've added a page called "Access" that contains two lists of names, one of which is the ManagerNames list, and the other is the list of people able to access the 'Leave' and 'Configuration' pages. The list of pages is now also on the 'Access' page.

I've changed the VBA code to reflect the change in location of the list of pages to the 'Access' page, cells C9:C12, but what I need to do essentially is get the list of authorised managers from a range, and populate the ManagerNames array from a range on the Access page (Access - cells G9:G17), rather than having it hard-coded in the macro, but I'm not sure how to achieve this - I don't know enough about VBA to work out how to change the code to do this without breaking it. The list of managers should always be the same length, but may change over time, or if an employee hasn't selected all the managers able to authorise their leave requests, so the length of the array needed may vary. For the pages the employee can access, again the list of pages is on the 'Access' page, cells C27:C28, and the list of people who can access these two pages is cells G27:G37. I've been reading about how to create VBA arrays, but there seem to be so many different methods of doing this, I can't figure out how to create an array of ManagerNames and EmployeeNames without breaking your code.

Essentially, I'm thinking that I need to run the same VBA action twice - once to secure the pages accessible only to managers and again to secure the pages accessible to the employee. I've managed to get the hidden pages bit of it working by simply repeating the same code again for the employee access by pasting the same code in again after your original code, but tweaked the 'Workbook_BeforeSave' and 'Workbook_Open' names slightly, but I'm aware there's a lot of duplicated code which I'm sure someone who knows VBA would condense.

Would you be able to advise the best way to create the two arrays of names and correctly detail the two ranges of hidden pages?

Many thanks,

Bliss

PS: This is where I've got to with the VBA code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim cell As Range

    For Each cell In Sheets("Access").Range("C9:C12")
        ActiveWorkbook.Worksheets(cell.Value).Visible = xlVeryHidden
    Next cell
    
End Sub

Private Sub Workbook_Open()
Dim cell As Range, ManagerNames As Variant

    ManagerNames = Array("Andrew Grainger", "Cathy Jones", "Paul Stepto", "Vanessa Watson", "Louise Fletcher", "Sam Naylor")
    If UBound(filter(ManagerNames, Application.UserName)) < 0 Then Exit Sub
    
    For Each cell In Sheets("Access").Range("C9:C12")
        ActiveWorkbook.Worksheets(cell.Value).Visible = True
    Next cell
    
End Sub

Private Sub Workbook_BeforeSaves(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim cell As Range

    For Each cell In Sheets("Access").Range("C27:C28")
        ActiveWorkbook.Worksheets(cell.Value).Visible = xlVeryHidden
    Next cell
    
End Sub

Private Sub Workbook_Opens()
Dim cell As Range, EmployeeNames As Variant

    EmployeeNames = Array("Andrew Grainger", "Cathy Jones", "Paul Stepto", "Vanessa Watson", "Louise Fletcher", "Sam Naylor", "Rachel Turner")
    If UBound(filter(EmployeeNames, Application.UserName)) < 0 Then Exit Sub
    
    For Each cell In Sheets("Access").Range("C27:C28")
        ActiveWorkbook.Worksheets(cell.Value).Visible = True
    Next cell
    
End Sub

 

[BlissC]

As the original subject of this thread isn't related to the current problem, I've now posted this current issue (securing both the set of manager accessible and employee accessible pages, and getting the names for the ManagerNames and EmployeeNames arrays from the ranges on the 'Access' page) on this thread: Help in adapting VBA code with runtime error 13 on array