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

Question

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?

What kind of combobox are you talking about? The combobox control or the one you can add to the Sheet with the "Data Validation" option? — Jacob, Aug 10 '11 at 06:54

score 56 · Accepted Answer · edited Oct 10 '14 at 21:15

56

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

edited Oct 10 '14 at 21:15

user2533406

61
4
13

answered Aug 10 '11 at 07:10

Jacob

41,721
6
79
81

1

Is there a way to do this programatically? – airstrike Dec 19 '11 at 19:11
1

@AndréTerra see the [answer](http://stackoverflow.com/questions/7006888/how-to-get-combobox-not-to-accept-user-input-in-excel-vba/19322189#19322189) I just posted if you are still curious :) – enderland Oct 11 '13 at 15:48

score 6 · Answer 2 · edited Jan 25 '13 at 09:56

6

YourComboBoxName.Style = fmStyleDropDownList

or

YourComboBoxName.Style = 2

(that's from MS Excel Help)

edited Jan 25 '13 at 09:56

Ivan Ferić

4,725
11
37
47

answered Jan 25 '13 at 09:27

Esmu Igors

239
3
6

score 5 · Answer 3 · answered Oct 11 '13 at 15:45

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

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

3 Answers3

Linked

Related