Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set CommandBars Dropdown value
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set CommandBars Dropdown value
What type of dropdown controls do you have?
Is .Caption what you are looking for? Dave D-C Paul Martin wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set CommandBars Dropdown value
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 On Jan 25, 6:04 am, Dave D-C wrote: What type of dropdown controls do you have? Is .Caption what you are looking for? Dave D-C Paul Martin wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set CommandBars Dropdown value
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 wrote: 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 On Jan 25, 6:04 am, Dave D-C wrote: What type of dropdown controls do you have? Is .Caption what you are looking for? Dave D-C Paul Martin wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set CommandBars Dropdown value
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 On Jan 25, 2:33 pm, Dave D-C wrote: 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 wrote: 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 On Jan 25, 6:04 am, Dave D-C wrote: What type of dropdown controls do you have? Is .Caption what you are looking for? Dave D-C Paul Martin wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
select From dropdown and return another dropdown menu | Excel Discussion (Misc queries) | |||
Dropdown box display only data dependent on another dropdown box? | Excel Worksheet Functions | |||
CommandBars vs CommandBars(1).Controls | Excel Programming | |||
populating a dropdown based on choice from a previous dropdown | Excel Programming | |||
offer dropdown options based on another dropdown | Excel Discussion (Misc queries) |