Drop Down List Triggering Macro

[Leeinsa]

I have a drop down box on a worksheet which allows users to select from a list. Depending on what they select, I then use the link cell to determine which rows in the sheet should be visible and which should be hidden.

All is well until I protect the sheet to stop the users from un-hiding and changing things they should not. When the sheet is protected, Excel wont let the Link cell update when the user selects from the drop down box, because the link cell is now locked.

So what i need is the following:

When the user clicks on their selection in the drop down box, The sheet to become unprotected, the relevant rows to be hidden or unhidden, then the sheet to be protected again.

Can anyone help me with the correct commands and more importantly where they should be to trigger it at the right time, I cannot find a way of running code when changes are made to the actual drop down list. (Hence the use of the link cell).

 

[Phox]

Enter design mode (available from the toolbar at View > Toolbars > Control Toolbox) and doubleclick on your combobox. VBE should open and put you in "Private Sub ComboBox1_Change()". This is where your code goes.

ActiveSheet.Unprotect
Rows("1:1").Hidden = True
Rows("12-17").Hidden = False
ActiveSheet.Protect

 

[Leeinsa]

Many thanks Phox, You just made number 2 on my hero list. Now i know how to get at the properties of the actual elements I use on my sheets. Thanks again.

Lee.