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. #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)

  3. #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
  •