Results 1 to 6 of 6
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a Dropdown Toobar Button (2000 sp3)

    I have no time to help with the sub-menu button code, but an advice I can give: move the code that creates the buttons out of the Workbook_Open sub to a sub in a nromal module and call that sub from workbook_Open.

    VBA editing causes build-up of garbage in the file (behind the scenes). This garbage can be removed using tools like Rob Bovey's code cleaner but that cannot be done to the thisworkbook module.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  2. #2
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating a Dropdown Toobar Button (2000 sp3)

    All,
    Recently, Hans, Steve and others have been assisting me on getting some VB to work. It works great!!! and Thanks to them.

    I now find that the users need to have a few of the buttons I created to sort, be in a drop down menu located in the toolbar. Below is the existing code for the creation of the buttons them selves, and the linkage to the macros. I do not believe you need the actual macros, but if you do, I can post later.

    The First button needs to be a standalone button, but the rest are buttons for various sorts. These can be in the dropdown.

    Thanks!,
    Brad

    Long code (almost 4,000 characters) moved to attachment by HansV

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

    Re: Creating a Dropdown Toobar Button (2000 sp3)

    Here is a macro to create a dropdown menu on a custom toolbar with two items. You can call this macro from the Workbook_Open event. You can modify and expand the macro as needed.

    Note 1: the toolbar will still be invisible when the macro finishes. As in your code, the Workbook_Activate and Workbook_Deactivate should take care of showing/hiding the toolbar.
    Note 2: you don't need to define new variables for each button you create; you can use one set and reuse them.

    Public Sub CreateSortDropdown()
    Dim cbr As CommandBar
    Dim cbp As CommandBarPopup
    Dim cbb As CommandBarButton

    Set cbr = Application.CommandBars.Add("Sort Bar")
    cbr.Position = msoBarTop

    Set cbp = cbr.Controls.Add(msoControlPopup)
    cbp.Caption = "Sort"

    Set cbb = cbp.Controls.Add(msoControlButton)
    With cbb
    .Caption = "CA Sort"
    .OnAction = "CA_Sort"
    .Style = msoButtonIconAndCaption
    .FaceId = 2174
    End With

    Set cbb = cbp.Controls.Add(msoControlButton)
    With cbb
    .Caption = "CR Sort"
    .OnAction = "CR_Sort"
    .Style = msoButtonIconAndCaption
    .FaceId = 2151
    End With

    Set cbb = Nothing
    Set cbp = Nothing
    Set cbr = Nothing
    End Sub

  4. #4
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a Dropdown Toobar Button (2000 sp3)

    Hans,
    Thanks for the code, however I am unclear on how to call the macro from the workbook_open. I am quite a novice at VB. Perhaps my company will pay for some classes. Can you suggest any books on VB?

    Thanks,
    Brad

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

    Re: Creating a Dropdown Toobar Button (2000 sp3)

    Calling a macro is easy:

    Private Sub Workbook_Open()
    Call CreateSortDropdown
    End Sub

    (You can omit the keyword Call if you prefer)

    The thread starting at <post#=435353>post 435353</post#> contains several suggestions and links about Excel VBA books.

  6. #6
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a Dropdown Toobar Button (2000 sp3)

    Hans,
    Again, Thanks. That worked great. I will re-create the rest of the buttons.

    I will review the thread you suggest.

    Regards,
    Brad

Posting Permissions

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