excel drop down menus

[RDKC]

is there a way to deactivate all the items within a drop down list, so that nothing can be clicked...only looked at? thank you

 

[Micron]

If this is on a userform and it's an activex control I'd say no. But you can easily deselect any selected item and make the combo empty again using vba:
Me.ComboBox1.ListIndex = -1

If it's not, then I don't know but would guess the same thing is possible. Combos on a sheet are a bit trickier. Why would you want this in the first place? Maybe a locked listbox would be better.

 

[RDKC]

If this is on a userform and it's an activex control I'd say no. But you can easily deselect any selected item and make the combo empty again using vba:
Me.ComboBox1.ListIndex = -1

If it's not, then I don't know but would guess the same thing is possible. Combos on a sheet are a bit trickier. Why would you want this in the first place? Maybe a locked listbox would be better.

it is just a simple excel worksheet...drop down menu works fine, i just don't want any of the list options to be select-able....i am happy with the text already in the active cell....also hoping not to use any 'protect sheet' options

 

[Micron]

AFAIK, then don't link the combo to any cell? The list comes from a range, but if the control isn't linked to a cell then picking a value does nothing.
Or you need to have it linked to a cell but don't want anyone to be able to change the value in that cell? Then protection is the simplest solution. It may be possible to unselect as I've noted, but you'd have to get the linked cell value during the drop down (if that's possible) in order to restore the old value. Not ideal IMHO.

 

[RDKC]

yes, the drop down list is linked to a range from another column, it is all text, no values...no idea what combos or control you are referring to...just want to have users click on the drop down and only be able to look and scroll through the list, but not click on anything...because i want to keep the text that is already in the cell...if they click on an item...that item then gets entered into the cell....i don't want that.....thanks

 

[Micron]

Well, I researched and experimented and can't see how it is possible to do exactly what you request. If the control is activex (you didn't clarify) then all I can foresee is removing the linked cell property. That would allow users to change the combo value but it won't affect the linked cell. If it's an ActiveX control, the property sheet looks like this

If it's a form control there is no real property sheet per se. Rather a dialog to format the control which looks like this

I presume you have the Developer tab on the Ribbon available and know how to use it.

 

[Fluff]

If you are talking about Data Validation, then the only way to stop people changing the value, is to protect the sheet.

 

[RDKC]

If you are talking about Data Validation, then the only way to stop people changing the value, is to protect the sheet.

yes, data validation, thank you. and also thank you to Micron

 

[Fluff]

You're welcome.

 

[Micron]

i just don't want any of the list options to be select-able... also hoping not to use any 'protect sheet' options

I guess I took that too literally. Data validation allows you to choose a list item without sheet protection and I answered as though sheet protection was to be avoided.
So you have a solution?