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

Does anyone know what the properties are in the combobox that I can manipulate in order not t开发者_如何学运维o allow the user to key/type in any data?

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 With
End Sub