Set dropdown programatically
 

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

Set dropdown programatically
 

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("myCombo Box").Text = _
Range("Value").Value

--
Regards

Melanie Breden
- Microsoft MVP für Excel -

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

Set dropdown programatically
 

"Melanie Breden" wrote in message
...
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"
' ...
' ...

Think you, I will try this!

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

--
Regards

Melanie Breden
- Microsoft MVP für Excel -

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

Set dropdown programatically
 

"Melanie Breden" wrote in message ...
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("myCombo Box").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?)

Set dropdown programatically
 

I have commandbar examples on my website

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Bura Tino" wrote in message
...
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

Set dropdown programatically
 

Hi,

Bura Tino wrote:
Application.CommandBars("MyBar").Controls("myCombo Box").Text = _
Range("Value").Value

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("myCombo Box").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)