VBA & Combo Box (Form Control) Lock

[jabobo422]

I'm trying to create a tracker that my employees must update. Each employee has their own worksheet. The first worksheet is a splash page where they can click their name and it will automatically jump to the worksheet assigned to them.

There is also an overall worksheet for their leadership team and myself to review all employees at once. I've locked all the cells on this worksheet so they cannot be edited without a password; with one exception.

Should an employee not meet a certain threshold, a specific cell associated with them will be highlighted on the overall worksheet. I have placed a 'Combo Box (Form Control)' object next to these highlighted cells. This drop-down box will allow leadership to quickly select whether it's okay or not that the employee didn't meet this threshold (employee was on leave, had other assignments, wasn't approved, etc.). I'm having trouble locking these drop-down boxes from being edited. I can protect the overall worksheet, but this only locks editing of the text/options of the drop-down. I can still freely change the selection of the drop-down even when the sheet is completely locked.

Is there a way to make it so users cannot select/manipulate/change the drop-down when the worksheet is protected/locked?

 

[Jaafar Tribak]

Hi & welcome to the board,

Are you protecting/unprotecting the worksheet via code or via the protect-sheet ribbon tab?

 

[jabobo422]

Hi & welcome to the board,

Are you protecting/unprotecting the worksheet via code or via the protect-sheet ribbon tab?

Just the ribbon tab. I’m pretty beginner with VBA and macros; and don’t know how to protect with code.

 

[Jaafar Tribak]

Just the ribbon tab. I’m pretty beginner with VBA and macros; and don’t know how to protect with code.

Ok- I think the only way you can achieve what you want is by toggling the DropDows Enabled Property to TRUE/FALSE. I don't know of a way to do this without resorting to vba.

- Hold the ALT key down and press F11. This will take you to the code editor.
- Add a new normal module to your project via Insert>Module from the vbe menu
- Copy the two following sub-routines and paste them in the Module white pane:

Sub EanbleDropDowns()
    Sheet1.DropDowns.Enabled = True
End Sub

Sub DisbaleDropDowns()
    Sheet1.DropDowns.Enabled = False
End Sub

- Change Sheet1 to match the actual codename of the worksheet where you have the form dropdowns.

- Save the workbook.

As the subroutine names suggest, the first one is for enabling all the dropdowns in the worksheet and the second one is for disabling them.

You can execute the above subroutines, either from withing the vb editor by placing the keyborad cursor inside the subroutines and pressing the F5 key, or by assigning the subroutines to buttons in your worksheet.

You should run the EanbleDropDowns subrouine after having unprotected the worksheet and run the DisbaleDropDowns subroutine after having protected it.

I hope I have clearly explained the required steps. If you have any problems, just ask.