Erlandsen Data Consulting

Let the macros themselves determine which CommandBar button that started them. If you attach the macro below to multiple CommandBar buttons, the messagebox will display different contents: Sub DummyMacro() If Application.CommandBars.ActionControl Is Nothing Then ‘ the macro was not started from a commandbar button MsgBox “This could be your macro running!”, vbInformation, _ “This macro [...]

The macro below shows how you can change the state of a custom CommandBar button so it displays as depressed or not. Sub ToggleButtonState() Dim m As CommandBarControl Set m = CommandBars("CommandBarName").Controls(1) If m.State = msoButtonDown Then m.State = msoButtonUp Else m.State = msoButtonDown End If Set m = Nothing End Sub

With the macro below, you can display a CommandBar centered on the screen, both horizontally and vertically. The macro also shows how you can get the screensize by using the function GetSystemMetrics32. Declare Function GetSystemMetrics32 Lib "User32" _ Alias "GetSystemMetrics" (ByVal nIndex As Long) As Long Sub CenterCommandBar() Dim w As Long, h As Long [...]

With macros it’s possible to create your own custom menu. In Excel-versions previous to Excel97 it is also possible to create menus with a built-in menu editor, but this option doesn’t exist in Excel97. So why not learn a method that works in both versions? The example below shows how you can create and delete [...]

In Excel97 and later, menus and toolbarbuttons can be manipulated with the CommandBar-object. Here are some example macros that will help you get started with the creation of your own menus: Sub CreateMenu() ‘ creates a new menu. ‘ can also be used to create commandbarbuttons ‘ may be automatically executed from an Auto_Open macro [...]

The example below shows how you can create CommandBar buttons/menus that passes one or more arguments to a macro. The example also shows how you can add a new item to the Cell shortcut menu. Sub AddCommandToCellShortcutMenu() Dim i As Integer, ctrl As CommandBarButton DeleteAllCustomControls ‘ delete the controls if they already exists ‘ create [...]

The macro below shows how you can toggle the availability state for the shortcut menus. Sub ToggleCommandBars() Dim cbEnabled As Boolean cbEnabled = Not CommandBars(25).Enabled CommandBars(25).Enabled = cbEnabled ‘ shortcutmenu for cells CommandBars(26).Enabled = cbEnabled ‘ shortcutmenu for columns CommandBars(27).Enabled = cbEnabled ‘ shortcutmenu for rows CommandBars("Toolbar List").Enabled = cbEnabled ‘ shortcutmenu for toolbars End [...]

The examples below uses the New-button on the Standard toolbar as an example. You can replace the toolbarnumber with the number or name of another Toolbar, and the number of the button you want to edit. Use this macro to change the tooltip text on custom or built-in toolbarbuttons: Sub ChangeToolTip() ‘ changes the tooltip [...]

The examples below uses the New-button on the Standard toolbar as an example. You can replace "Standard" with the name or number of another CommandBar, and the number of the Control you want to edit. Use this macro to change the tooltip text: Sub ChangeToolTipsText() Application.CommandBars("Standard").Controls(1).TooltipText = "Test" End Sub Use this macro to reset [...]

The macro below shows how you can toggle the availability state for a menu item: Sub ToggleMenuControls() Dim m As CommandBarControl, mi As CommandBarControl Set m = CommandBars.FindControl(ID:=30002) ‘ File Menu If m Is Nothing Then Exit Sub For Each mi In m.Controls If mi.ID = 18 Then mi.Enabled = Not mi.Enabled ‘ toggles the [...]