Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Command Bars (VBA/Excel/2003 SP2)

    I am able to create a command bar with a series of, pop-ups successfully (thank you Lounge members). However, I am stumped when it comes to creating a simple command bar that will run a procedure when clicked. I am suspicious that some of my problems are the consequence of the Help examples not declaring variables; so if declarations were included in any sample code, I would be ever so grateful.

    As a distant second alternative, I will place the button in the Menu Bar, but the following recorded macro leaves too many unknowns in my mind.

    <pre>Sub Macro6()
    '
    ' Macro6 Macro
    ' Macro recorded 12/6/2006 by c9552
    '
    '
    Application.CommandBars("Worksheet Menu Bar").Controls.Add Type:= _
    msoControlButton, ID:=2949, Before:=11
    End Sub
    </pre>

    T.I.A.
    Regards
    Don

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

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

    Re: Command Bars (VBA/Excel/2003 SP2)

    Command bars don't do anything special when clicked, so I'm not sure what you mean. If you mean a command bar button, you can use code like this:

    Dim cbr As CommandBar
    Dim cbb As CommandBarButton
    Set cbr = CommandBars.Add(Name:="My Toolbar", Temporary:=True)
    cbr.Visible = True
    Set cbb = cbr.Controls.Add(Type:=msoControlButton, Temporary:=True)
    With cbb
    .Caption = "My Button"
    .Style = msoButtonCaption
    .OnAction = "MyButtonProc"
    .Visible = True
    End With
    Set cbb = Nothing
    Set cbr = Nothing

    MyButtonProc must be the name of a macro.

    (I set Temporary:=True because I didn't want the toolbar to remain in existence after this session, you can omit that if you wish)

  4. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Command Bars (VBA/Excel/2003 SP2)

    Thank you Hans.

    I never fail to be rewarded when posing a question to the Lounge.

    Many thanks again.
    Regards
    Don

Posting Permissions

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