Protect a ComboBox

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.
IBMJunkmanAsked:
Who is Participating?
 
rspahitzCommented:
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.

0
 
rspahitzCommented:
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
0
 
IBMJunkmanAuthor Commented:
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.
0
 
IBMJunkmanAuthor Commented:
Thanks.  I got it working.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.