Can I set an Excel ComboBox to have a default valu

Question

My ComboBox (named ddDatabase) can have either one of two values. These two values are pulled from a table in a separate worksheet. When I open the Excel file, however, neither of the two values are selected. Instead the ComboBox appears empty. Is there any way to explicitly set a specific value to be the default?

After some further testing, it appears that if the Excel file is saved with the first item of the ComboBox selected, the next time the file opens it will default to blank. Selecting any other value in the ComboBox other than the first will preserve the selection after you save, close, and re-open.

I've tried to use VBA to solve this issue by setting the value when the workbook is opened using this code:

Sub Workbook_Activate()

    Dim ddDatabase As DropDown
    Set ddDatabase = ActiveSheet.DropDowns("ddDatabase")
    ddDatabase.Value = 1

End Sub

Unfortunately, it throws the following error:

unable to set the value property of the dropdown class

Is there a solution to this?

Answer 1

When I tried it in Excel 2013, it was as simple as selecting either of the two items in that ComboBox and then saving the workbook. Next time I opened it, the one I selected showed up as the "default".

It even worked when I set one of the two source cells to =NOW() (so that the displayed value would be different next time I opened it), then selected that and saved it. Next time I opened, it still had the (updated) value of =NOW() displayed in the ComboBox.

Unless you need a pure-VBA solution?

Answer 2

You can manually set it. Maybe do it on the workbook_open event.

ComboBox1.Text = "SomeText"

Answer 3

Make your default value the first option in the drop down.

With ActiveSheet.Shapes("ddDatabase").ControlFormat
    .value = 1
End With

Answer 4

Use this:

.ComboBox1.Text = .ComboBox1.List(1) 'Change the number to the value you want as default. 
'If you want to default to something like 'select a value' write it as a string

You have to specify the value yourself at creation (or on workbook open, or form open if it is in a form).

Edit:

You might want to change it to a validation list if that's more flexible for you:

    With f_overview.Range("cell_act").Validation 'Change this to something like Sheets("Sheet1").Range("A1").Validation
        .delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Acteurs!$E$4:$E$23" 'Change this to "=Sheet1!$A$1:$A$2"
    End With

The first commented line is for the emplacement of the list, the second one is your source values (your two values).