CODEWITHSUNDEEP
excelvba
Ho Jun Hong
Ho Jun Hong

Reputation: 339

How to choose a starting value in a dropdown in VBA?

I have created a dropdown at a specific cell and assigned values to them. However I do not want to show a blank value at default. What code should I use to set the default value of the dropdown to value 500 (not the positions values of the dropdownlist)? There is this link Can I set an Excel ComboBox to have a default value? that was given to me before but this is not what I want because I am not looking at a validation list. I have tried - 

With ActiveSheet.Shapes("Combo Box 1").ControlFormat
    .value = 500
End With

but it does not work. Is there a way to do it?

Upvotes: 1

Views: 1164

Answers (1)

JvdV
JvdV

Reputation: 75840

A little more difficult than I thought at first. If you are talking about a Form control instead of ActiveX you want to set your .Text property (just as per linked answer).

However, once you have set your .Text property you'll end up changing values but no visual change in your ComboBox. One way to overcome this is a .OnAction refering to a Sub to change that .Text property again according to it's .Value. And because .Value on its turn would return the Index we can use that on the .List property.

Below a sample code to replicate what I'm talking about:

Sub Test()

Dim Bx As Object

'Add ComboBox for testing
Set Bx = Worksheets("Sheet1").DropDowns.Add(0, 0, 100, 15)
Bx.Name = "Combo Box 1"

'Add values to box for testing
Bx.AddItem 100
Bx.AddItem 200
Bx.AddItem 300
Bx.AddItem 400
Bx.AddItem 500

'Create the possibility to change .Text property according to .Value
Bx.OnAction = "StandardVal"

'Set standard value
Bx.Text = 500

End Sub

Sub StandardVal()

With Worksheets("Sheet1").DropDowns("Combo Box 1")
    .Text = .List(.Value)
End With

End Sub

In the case of OP, he can set .Text property instead of .Value and assign a macro (for example the above sample macro "StandardVal") to the ComboBox

Upvotes: 1

Related Questions