How to get combobox not to accept user input in Excel-Vba? How to get combobox not to accept user input in Excel-Vba? vba vba

How to get combobox not to accept user input in Excel-Vba?


Set the the Style of the combobox to 2 - fmStyleDropDownList. This will disallow user input, and will also prevent (combobox).value changes via macro.


YourComboBoxName.Style = fmStyleDropDownList

or

YourComboBoxName.Style = 2

(that's from MS Excel Help)


Here's a way to change this for each object on a worksheet:

Private Sub fixComboBoxes()    Dim OLEobj As OLEObject    Dim myWS As Worksheet    Set myWS = Sheet1    With myWS        For Each OLEobj In myWS.OLEObjects            If TypeOf OLEobj.Object Is MSForms.ComboBox Then                OLEobj.Object.Style = fmStyleDropDownList            End If        Next OLEobj    End WithEnd Sub