CODEWITHSUNDEEP
excelvbacontrolsexcel-2007
Vivian
Vivian

Reputation: 1061

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 to allow the user to key/type in any data?

Upvotes: 23

Views: 99104

Answers (3)

Jacob
Jacob

Reputation: 43229

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

Upvotes: 57

enderland
enderland

Reputation: 14145

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

Upvotes: 5

Esmu Igors
Esmu Igors

Reputation: 249

YourComboBoxName.Style = fmStyleDropDownList

or

YourComboBoxName.Style = 2

(that's from MS Excel Help)

Upvotes: 6

Related Questions