Protect a ComboBox

Posted on 2011-10-26
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
    LVL 22

    Expert Comment

    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

    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

    Ah, so it's showing the'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

    Thanks.  I got it working.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Suggested Solutions

    Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now