MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How I make the dropdown list as readonly


Posted by Pooja on November 30, 2000 6:22 PM

Hi,

I have created a pull down menu for cell thru a VBA
macro.

Now I want to make that pulldown.....just readonly.
THe user can just see the options but he shouldn't
not be allowed to select any options.

How do I need to do that?

Please help me as soon as possible.

I have used..Selection.validation object.

But please tell me the various other properties
to be set to achieve this

TIA
Pooja


Posted by Tim Francis-Wright on December 01, 2000 1:53 PM

There might be a non-VBA way to do something
similar. Set up the cell in question
to have Data Validation, with the valid cells
entries in the list (or range) that you want.
Then have the format of the cell be ;;; (hidden).
If all you want is to show the dropdown box, and
the cell doesn't affect any formulas, then
this will have the intended effect.

-------------OR--------------

If, as I suspect, you want to have the results of
the drop-down box mean something, but you just
want to have the box be read-only, use the
combobox from the Control Toolbox menu.

The code for the change method should look
something like this:

'this will be in the Sheet object for the Sheet where the combobox is
Private Sub ComboBox1_Change()
ComboBox1.Value = "www.mrexcel.com"
' if the user tries to change the best website
' combobox, we change it back!
End Sub

You could, of course, have code in the
ComboBox1_Change sub to change the value only if
certain things were true.

HTH