Custom Bar buttons

[Navvy]

Hello! I am looking to create a button on my custom bar that once clicked will bring up a list. So, for example, if I hit Button1 then it will bring up a list with List1 and List2 which I will correspond to some macros that I have recorded.

 

[TonyJollans]

Hi Navvy,

You can't make a Button do that; you need to make it a Popup. The easiest thing to do is record yourself doing it manually and then tweak the code.

To create a new command bar:

Goto Tools > Customize
Click on the Toolbars tab
Click on New...
Give it whatever name you want and click on OK

To add a popup to your new command bar:

Select the Commands tab
Under Categories Select New Menu
Click and Drag New Menu from under Commands to your Command Bar
Right click the menu that has been added to change its name

To add items to this new menu:

Still on the Commands tab
Under Categories Select New Menu
Click and Drag Custom Button from under Commands to your new menu
Right click the button that has been added to change its properties
Repeat as often as necessary

With a little bit of experimenting you have all you need in no time.

Enjoy,
Tony

 

[rdodge]

Once you have setup your shortcut (or popup) menus (as explained by Tony), you can then see about creating a toolbar button, create a VBA macro.

Your VBA macro will have the following line of code:

<CommandBarObject>.ShowPopup(X,Y)

A couple of notes about the coordinates. These coordinates are first off measured in twips, so be careful as it makes a difference with regards to screen resolutions. Just as a reference point, there are 1440 twips per inch regardless of the screen resolution.

The other thing, these coordinates are optional. If they are no arguments provided, the menu will pop up at the point of where the mouse pointer is located at.

References to this information:

Microsoft Access 2002 Help>Programming in Visual Basic>Microsoft Office Visual Basic Reference>Objects>C>CommandBar Object

Microsoft Access 2002 Help>Programming in Visual Basic>Microsoft Office Visual Basic Reference>Methods>S-U>ShowPopup Method

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[pragmatic]

I don't know about buttons, but this code will add a menu item under TOOLS in your menu bar.

    Dim oMenu As CommandBarPopup
    Dim oItem As CommandBarPopup
    Dim oSub1 As CommandBarButton
    Dim oSub2 As CommandBarButton
    
    CommandBars("worksheet menu bar").Reset
    
    Set oMenu = CommandBars("Worksheet Menu Bar") _
        .Controls("Tools")
        
    Set oItem = oMenu.Controls.Add(Type:=msoControlPopup)
    oItem.Caption = "ADScript"
    ' ADScript is the sub menu item under TOOLS

    Set oSub1 = oItem.Controls.Add(Type:=msoControlButton)
    oSub1.Caption = "3 Column Number to 1 Column"
    oSub1.OnAction = "formatnumber"
    ' formatnumber is a sub module

    Set oSub2 = oItem.Controls.Add(Type:=msoControlButton)
    oSub2.Caption = "Insert Blank Lines"
    oSub2.OnAction = "blankspc"
    ' blankspc is a submodule