Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Location
    Princeton, New Jersey, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Custom Dropdown Button on Toolbar (Excel 97/2000)

    I have an Excel macro that creates a custom toolbar containing custom buttons. The buttons are added with the following code:
    With cbCustomBar
    Set muCustom = .Controls.Add(Type:=msoControlButton, before:=1)

    I'd like to add a custom dropdown button, like the pre-programmed button for font color. MS help lists a control type "msoControlButtonDropdown", but when I substitute that type in my code, the de###### stops at that line with Runtime error 5 (invalid procedure call or argument).

    I saw a similar post from Oct 2002, but the only response suggested using type "msoControlPopup" instead, but that won't satisfy my user requirements. The dropdown button is ideal, because it retains the user's "selection" as the active button until they change it, allowing them to easily use the same choice over and over. If this can't be done, I'll resort to separate buttons for each item. My only concern with that solution is the greatly increased length of the toolbar.

    Thanks in advance for any suggestions!

    Kris

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Custom Dropdown Button on Toolbar (Excel 97/2000)

    I don't know very much about Excel, but on general principle, I believe one is permitted to duplicate the built-in CommandBarControls onto custom toolbars if one wants. That said, I'm not sure how to do it programmatically. Perhaps there is a .CopyFrom, .AddFrom or some method like that which will do the trick.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom Dropdown Button on Toolbar (Excel 97/2000)

    Here is an example using an msoControlDropDown, not an msoControlButtonDropDown:

    Sub MakeComboBox()
    Dim cbr As CommandBar
    Dim ctl As CommandBarComboBox
    On Error GoTo Err_Handler
    Set cbr = CommandBars("Standard")
    Set ctl = cbr.Controls.Add(msoControlDropdown)
    ctl.Caption = "Mark By"
    ctl.BeginGroup = True
    ctl.Style = msoComboLabel
    ctl.AddItem "None"
    ctl.AddItem "Color"
    ctl.AddItem "Price"
    ctl.AddItem "Year"
    ctl.ListIndex = 1
    ctl.OnAction = "MyAction"
    Exit_Sub:
    Set cbr = Nothing
    Set ctl = Nothing
    Exit Sub
    Err_Handler:
    MsgBox Err.Description, vbExclamation
    Resume Exit_Sub
    End Sub

    The macro MyAction tests which item has been selected and runs the appropriate code:

    Public Sub MyAction()
    Dim strChoice As String
    On Error GoTo Err_MyAction
    strChoice = CommandBars("Standard").Controls("Mark By").Text
    Select Case strChoice
    Case "None"
    ' action here
    Case "Color"
    ' action here
    Case "Price"
    ' action here
    Case "Year"
    ' action here
    End Select
    Exit_MyAction:
    Exit Sub
    Err_MyAction:
    MsgBox Err.Description, vbExclamation
    Resume Exit_MyAction
    End Sub

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Dropdown Button on Toolbar (Excel 97/20

    There is no msoControlButtonDropdown. The control you have in mind is a msoControlSplitButtonPopup.

    One of the ways of testing for this in future, is to use the following code:<pre>Sub ControlType()
    Dim cBar As CommandBar
    Dim ctl As CommandBarControl

    For Each ctl In CommandBars("Formatting").Controls
    ctl.TooltipText = ctl.Type
    Next

    End Sub</pre>

    You can then verify the Type number using the Object Browser for MsoControlType. To reset the tooltips, rerun the code substituting .Caption for .Type.
    Gre

  5. #5
    New Lounger
    Join Date
    Sep 2002
    Location
    Princeton, New Jersey, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Dropdown Button on Toolbar (Excel 97/20

    Thank you for your help. According to MS help that control type does exist (that's where I found it), but I know that their help does occasionally have errors in syntax. In any case, I had already tried the msoControlSplitButtonPopup, since that would fit my needs, but that doesn't work, either. Have you successfully used this for a custom popup button?

    Kris

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Dropdown Button on Toolbar (Excel 97/20

    The control exists sure enough, but Excel doesn't actually have one! <img src=/S/smile.gif border=0 alt=smile width=15 height=15> There's a recent discussion of it on this on the website of Axel Herber, a German Excel MVP. If you can read German, it is here.

    The trouble about the msoControlSplitButtonPopup is that there doesn't seem to be an "exposed" way to populate it. Translation: Microsoft doesn't seem to want to tell us how (yet). (Only with Excel 2000 and above are you even able to add a default msoControlSplitButtonPopup to a custom toolbar.)

    If screen real estate is an issue, the only workaround I can suggest is a floating Shortcut (aka Right-Click) Menu. This wouldn't, however, deal with your issue of retaining the previous choice.

    HTH
    Gre

  7. #7
    New Lounger
    Join Date
    Sep 2002
    Location
    Princeton, New Jersey, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Dropdown Button on Toolbar (Excel 97/20

    Thank you so much! I suspected it was something like that. My users will just have to deal with a long toolbar for now.

    Kris

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Dropdown Button on Toolbar (Excel 97/2000)

    <P ID="edit" class=small>(Edited by AlanMiller on 07-Nov-05 06:35. Question answered... mostly, that is.)</P>Hans,

    Looking at your example, is it possible to use/ adapt this on an existing custom template commandbar like this: use the Sub MakeComboBox() once to make the modification to the commandbar, then resave the template - then rely on Public Sub MyAction() to "work" the commandbar in new documents, created off the template? Hope that doesn't sound too confusing.

    Alan

    Edit - I can see that the two routines can be used independently, but what threw me a little was the fact that there's nothing visible at the interface level (Customize toolbars) to indicate the action connected to the combo, once it's been created/ added i.e. the MyAction macro. I guess this is accessible only by (re)locating the control via code?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom Dropdown Button on Toolbar (Excel 97/2000)

    If you select Tools | Customize..., right-click the dropdown and select Assign Macro..., you'll see the name of the On Action macro, i.e. Workbookname!MyAction.

  10. #10
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Dropdown Button on Toolbar (Excel 97/2000)

    I'm afraid I don't get that option at all for the custom menu I've built (although I know I've seen it). I see the greyed out one below. The connection is there to the macro alright, but I can see no way of revealing it at the interface. This is in a Word 2000 template, and the other snapshot is for another menu item to which a macro was assigned "manually", through the customize dialog.

    Alan

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom Dropdown Button on Toolbar (Excel 97/2000)

    Ah yes, the original question was about Excel, and you didn't indicate otherwise in your first question. so I assumed you were using Excel too. In Word, the name of the On Action macro cannot be viewed or assigned in the Word interface once the control has been created, it requires code.

  12. #12
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Dropdown Button on Toolbar (Excel 97/2000)

    Thanks Hans. That's another Office app difference I didn't know about - as you see, I'm quite ignorant of the UI with regards menus. I think my first serious attempt in XL resulted in strange conflicts on client machines, and the gurus in the Lounge convinced me early on about using a create/ destroy approach via VBA. I think I've used code ever since, except for some very trivial instances.

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •