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