Set dropdown programatically

[Bura Tino]

Hi,

I know how to read from a toolbar dropdown

Range("Value") = CommandBars("MyBar").Controls("myDropDown").Text

But suppose I change Range("Value") in some other way (e.g. in a sub) and
want to synchronize the toolbar with the value in the cell. Can I do so
programatically?

Thanks!

Bura

 

[Melanie Breden]

Hi Bura,

I know how to read from a toolbar dropdown

Range("Value") = CommandBars("MyBar").Controls("myDropDown").Text

But suppose I change Range("Value") in some other way (e.g. in a sub) and
want to synchronize the toolbar with the value in the cell. Can I do so
programatically?

use a ComboBox instead a DropDown Control:

With Application.CommandBars.Add("MyBar", Temporary:=True)
With .Controls.Add(msoControlComboBox)
.Caption = "myComboBox"
' ...
' ...


Application.CommandBars("MyBar").Controls("myComboBox").Text = _
Range("Value").Value

--
Regards

Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)

 

[Bura Tino]

Hi Bura,


use a ComboBox instead a DropDown Control:

With Application.CommandBars.Add("MyBar", Temporary:=True)
With .Controls.Add(msoControlComboBox)
.Caption = "myComboBox"
' ...
' ...
Think you, I will try this!

Application.CommandBars("MyBar").Controls("myComboBox").Text = _
Range("Value").Value

--
Regards

Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)

 

[Bura Tino]

Hi Bura,


use a ComboBox instead a DropDown Control:

With Application.CommandBars.Add("MyBar", Temporary:=True)
With .Controls.Add(msoControlComboBox)
.Caption = "myComboBox"
' ...
' ...


Application.CommandBars("MyBar").Controls("myComboBox").Text = _
Range("Value").Value

Hmmm. I still get "Invalid procedure call or argument".

(What's the difference between a drop down and a combo box?)

 

[Rob van Gelder]

I have commandbar examples on my website

 

[Melanie Breden]

Hi,

Hmmm. I still get "Invalid procedure call or argument".

in which line stops the debugger?
Here is the complete Code, (tested from XL97 to XL2003 with Win XP):

Sub DropDownBar()
On Error Resume Next
Application.CommandBars("MyBar").Delete
On Error GoTo 0

With Application.CommandBars.Add("MyBar", Temporary:=True)
With .Controls.Add(msoControlComboBox)
.Caption = "myComboBox"
.AddItem "A"
.AddItem "B"
.AddItem "C"
.OnAction = "ComboValue"
End With
.Visible = True
End With
End Sub

Sub ComboValue()
Range("Value") = CommandBars("MyBar").Controls("myComboBox").Text
End Sub

Sub SetDropDownValue()
Application.CommandBars("MyBar").Controls("myComboBox").Text = _
Range("Value").Value
End Sub

(What's the difference between a drop down and a combo box?)

It´s impossible to show an entry/value in a dropdown control with using VBA.
You have to use a comboBox.

--
Regards

Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)