Set CommandBars Dropdown value

[Paul Martin]

Hi

- I have a number of dropdown controls on a custom toolbar.
- On Workbook_Close, I save to a worksheet the ListIndex properties of
each dropdown control.
- On Workbook_Open, my code builds the custom toolbar, and the user's
last dropdown selections are restored from the saved ListIndex values
stored in the worksheet.

- What I would like to do is save the Text property of the dropdown
control and then reset it at startup.
- The Text property appears to be read-only, so I can get it but not
set it.

Q1. Is there a property similar to Text that is read-write, so that I
can set the property?
Q2. ListIndex and Text are at least two properties of the dropdown's
properties that do not appear to be documented, nor are they available
with the VBE's Intellisense (dropdown lists when one types a period
after the object). Is there a definitive list somewhere that
documents these 'hidden' properties.

Thanks in advance

Paul Martin
Melbourne, Australia

 

[Dave D-C]

What type of dropdown controls do you have?
Is .Caption what you are looking for? Dave D-C

 

[Paul Martin]

Hi Dave

The control is a CommandBarControl where the Type is
msoControlDropdown. .Caption is not sufficient as it is simply the
name (and default tooltip) for the control but not the value of the
control.

Regards

Paul

 

[Dave D-C]

Paul,
Q1 - I find that ListIndex is read/write.
Q2 - I agree that ListIndex and Text don't appear with
the intellisense.

Isn't ListIndex (being read/write) just what you want?
I think you've got it!

The following is my code: (I'm XL97, but I don't think
there's any difference)
Option Explicit
Dim CB1 As CommandBar
Dim CBC1 As CommandBarControl, CBC2 As CommandBarControl

Sub Sub1()
On Error Resume Next
Application.CommandBars("CB1").Delete
On Error GoTo 0 ' restore error processing
Set CB1 = Application.CommandBars.Add("CB1", msoBarFloating, False,
True)
CB1.Visible = True
' dropdown1
Set CBC1 = CB1.Controls.Add(msoControlDropdown)
CBC1.Style = msoComboNormal ' msoComboLabel Or msoComboNormal
CBC1.Caption = "CaptionDropdown1"
CBC1.AddItem "Item1A"
CBC1.AddItem "Item1B"
CBC1.OnAction = "SubDropdown1"
' dropdown2
Set CBC2 = CB1.Controls.Add(msoControlDropdown)
CBC2.Style = msoComboNormal ' msoComboLabel Or msoComboNormal
CBC2.Caption = "CaptionDropdown2"
CBC2.AddItem "Item2A"
CBC2.AddItem "Item2B"
CBC2.OnAction = "SubDropdown2"
End Sub ' Dave D-C

Sub SubDropdown1()
Call ShowAll("SubDropdown1 Before")
CBC1.ListIndex = 1
CBC2.ListIndex = 1
Call ShowAll("SubDropdown1 After")
End Sub

Sub SubDropdown2()
Call ShowAll("SubDropdown2 Before")
CBC1.ListIndex = 2
CBC2.ListIndex = 2
Call ShowAll("SubDropdown1 After")
End Sub

Sub ShowAll(pMsg$)
Debug.Print "--" & pMsg
Debug.Print CBC1.Caption, CBC1.ListIndex, CBC1.Text
Debug.Print CBC2.Caption, CBC2.ListIndex, CBC2.Text
'Debug.Print CBC1.Item(CBC1.ListIndex) ' gives Run-time error '438'
End Sub

 

[Paul Martin]

Hi Dave

I am currently writing the ListIndex property to the worksheet at
Workbook_Close and then setting the ListIndex from the worksheet at
Workbook_Open. So I am already doing in essence what I want. But I
would like to do the same by writing the Text value to the worksheet
(which I can do now), and then setting the dropdown value based on
this Text value. But I don't know the relevant read-write property
(if one exists) of the dropdown.

Regards

Paul