Set dropdown programatically

  • Thread starter Thread starter Bura Tino
  • Start date Start date
B

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
 
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?
Click to expand...

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)
 
Melanie Breden said:
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)
Click to expand...
 
Melanie Breden said:
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
Click to expand...

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

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

Bura said:
Hmmm. I still get "Invalid procedure call or argument".
Click to expand...

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?)
Click to expand...
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)
 
Back
Top