Protect a ComboBox

Posted on 2011-10-26
Medium Priority
Last Modified: 2013-11-05
I have sheet with 2 combo boxes.  After the user selects entries from each I want to lock the boxes so they can't change their selection.  I know I can protect the cell where the dropdown index gets stored but I also need to keep the combobox from being changed since if it did the shown value would not match the previous stored value.
Question by:IBMJunkman
  • 2
  • 2
LVL 22

Expert Comment

ID: 37033969
It depends what kind of combobox you used.
If you got it from the ActiveX section, you can right-click to view properties and notice that there is one for enabled.

So you can add some code like this:

Private Sub ComboBox1_Change()
    ComboBox1.Enabled = False
End Sub

Author Comment

ID: 37034464
OK, changed to an ActiveX box.  I have a named range entered in the ListFillRange and that works.  But when I use the control it puts the selected value in the LinkedCell, not the ordinal like the other type of box does.
LVL 22

Accepted Solution

rspahitz earned 500 total points
ID: 37035996
Ah, so it's showing the index...you'll need to unlink (clear the LinkedCell property) that and use something like the following:

Private Sub ComboBox1_Change()
    Range("C1").Value = ComboBox1.ListIndex + 1
    ComboBox1.Enabled = False
End Sub

Obviously, you'll need to change a few things.
Instead of "C1" use the cell that you had in the LinkedCell property.
And, ComboBox1 should be whatever the combobox is called (the name you see in the Excel Name box when you select it, or the name that showed up in VB when you double-click it.)

Let me know how that looks.


Author Closing Comment

ID: 37041001
Thanks.  I got it working.

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question