Excel – Unlocking Locked Drop Down boxes in Excel

data validationmicrosoft exceltroubleshooting

I have a couple of drop down boxes on a form that are what I'd like to dub "perma-locked". Every person we've thrown at the form is unable to unlock the drop down.

The drop downs do not show a context menu when right clicked and when they're the active item, all menu bars turn grey. The drop downs cannot be dragged or resized. They have a very distinct layout in that that are completely fixed. If I make the cell that they are in bigger or smaller, they do not resize. There are no macros in the workbook and the workbook isn't protected.

Anybody have a clue how to get control of these?

Best Answer

I found the culprit! The item is a Combo Box (ActiveX Control). It can be manipulated when in Design View which can be accessed via:

In Office 2003 :: View -> ToolBars -> Control Toolbox -> Design Mode 
In Office 2007 :: Developer Tab -> Design Mode

Related Solutions

Excel, drop down lists

That's because that's not how data validation works in excel. You need to use an active X control. Refer to the Contextures data validation page in general and her ComboBox Data Validation and ComboBox Depend examples in particular.

Update

If you do use her examples, replace the TempCombo_KeyDown event handler with TempCombo_KeyUp. At least on my excel (excel 2003 SP2) it crashes.

More gory details about the example I pointed you to:

I'm not sure why changing TempCombo_KeyDown to TempCombo_KeyUp works, but it may be related to KeyCode. MSDN implies that KeyCode is actually a pointer and is for all practical purposes passed by ref, even though the signature says ByVal. The crash always happens on the End Sub line of TempCombo_KeyDown.

This is just guessing on my part, but by processing the KeyDown event, after the new cell is activated, if it has TempCombo, then the object is activated & can process the KeyUp event. If the target cell doesn't activate TempCombo, the KeyUp event has no valid object reference and you get a nasty crash.

Excel – Using a drop-down list to populate other drop-down lists

Put a list of fruits in column C, a list of dogs in column D and a list of cats in column E. The DV dropdown in cell A1 will allow the user to select either "fruit" or "dog" or "cat".

In cell A2 we setup Data Validation with the List option and the formula:

=IF(A1="fruit",C1:C4,IF(A1="dog",D1:D4,E1:E4))

Now if you pick a category in A1, cell A2 will show the items for that category !

From My Old Post

Best Answer

Related Solutions

Excel, drop down lists

Excel – Using a drop-down list to populate other drop-down lists

Related Question