Unselectable items in a Combo Box in an Excel VBA

dunkelme Published at Dev

dunkelme

I have a combo box (in a user form in Excel) whose data source is set of menu items with headers (a named range in a worksheet). Right now, my workaround is to erase these headers (e.g. Main Course, Desserts, Beverages, etc.) but I would still like to know if it's possible to add these unselectable headers so that the end user has distinctions between the different menu items. Any help would be very much appreciated :)

e.g.

Main Courses (**unselectable**)
Roast Beef with Mashed Potato (selectable)
Spicy Spareribs (selectable)
Beef Stroganoff (selectable)
Roast Chicken (selectable)
Desserts (**unselectable**)
Mango Float (selectable)
Brownies (selectable)
Lemon Squares (selectable)

Siddharth Rout

No AFAIK, you can't do that. Below are two alternatives. Take your pick :)

Alternative 1

Deselect the moment user selects the relevant header. For example

Private Sub UserForm_Initialize()
    ComboBox1.Style = fmStyleDropDownList
    ComboBox1.AddItem "---  Main Courses ---"
    ComboBox1.AddItem "Roast Beef with Mashed Potato"
    ComboBox1.AddItem "Spicy Spareribs"
    ComboBox1.AddItem "---  Desserts  ---"
    ComboBox1.AddItem "Mango Float"
    ComboBox1.AddItem "Brownies"
End Sub

Private Sub ComboBox1_Click()
    Select Case ComboBox1.Value
    Case "---  Main Courses ---", "---  Desserts  ---"
        ComboBox1.Value = ""
    End Select
End Sub

Alternative 2

Use two comboboxes. One for the header and the other for the different menu items. Simply populate the second combobox based on the selection of the first combobox. For example

Private Sub UserForm_Initialize()
    ComboBox1.Style = fmStyleDropDownList
    ComboBox2.Style = fmStyleDropDownList
    ComboBox1.AddItem "---  Main Courses ---"
    ComboBox1.AddItem "---  Desserts  ---"
End Sub

Private Sub ComboBox1_Click()
    ComboBox2.Clear

    Select Case ComboBox1.Value
    Case "---  Main Courses ---"
        ComboBox2.AddItem "Roast Beef with Mashed Potato"
        ComboBox2.AddItem "Spicy Spareribs"
    Case "---  Desserts  ---"
        ComboBox2.AddItem "Mango Float"
        ComboBox2.AddItem "Brownies"
    End Select
End Sub

Also to be on a safer side, use the ComboBox1_Change event.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-02-13

Comments

0 comments

From Dev

Related Related

Article

Unselectable items in a Combo Box in an Excel VBA

Unselectable items in a Combo Box in an Excel VBA

Excel VBA adding items to combo box without repeated items

Excel VBA adding items to combo box without repeated items

Combo Box in VBA Excel

Excel VBA: Looping through a combo box's items from another user form

Assigning values to Combo box in excel VBA

Assigning values to Combo box in excel VBA

Excel VBA: how to store combo box current value to cell

Activate combo-box action on select value using Excel VBA

Excel VBA Replace data validation lists with combo box

How to send these items in the combo box

Set items to Combo Box in JAVA

VBA: Initialize Combo Box in worksheet

Combo box custom items and data bound items

Combo box custom items and data bound items

Excel Combo Box Refresh Dropdown?

How to add BOTH static values and records from a table as list items to a combo box in VBA

How to Open Drop down (combo items) of Combo Box on KeyDown Event?

How to Open Drop down (combo items) of Combo Box on KeyDown Event?

Adding items to combo box in a table in netbeans

How to add items from combo box into array

How to add data table items in a combo box?

Edit Win32 combo box items

Adding items to combo box in a table in netbeans

Selected value in combo box to open another worksheet in Excel VBA/User form

Excel VBA: Why does combo box shows only one item in the list?

Scraping website data with options in combo box VBA

VBA Add combo box values together

Adding items from a database to a combo box and a text box

Excel Combo box conditional If statement to detect blank