Enhancing User Interaction with Command Bars and Buttons in Excel

When writing professional applications in Excel, utilizing CommandBars and buttons can streamline user interaction. By creating custom menus and buttons, you can enhance user experience, making tasks more accessible and intuitive. This article explores how to utilize CommandBars to customize Excel's menu structure, add new commands, and create a professional look for your application.

• Excel
• CommandBars
• Buttons
• User Interaction
• Macro Coding

almadel Follow

Uploaded on Oct 06, 2024 | 0 Views

Download Presentation

Please find below an Image/Link to download the presentation.

The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.

Presentation Transcript

1. ITEC397 Macro Coding 10 COMMAND BARS AND BUTTONS

2. Command Bars and Buttons 2 When writing professional applications, it s important to remember that the less the user has to do to interact with the application, the better. Users tend to want everything as easy as possible and do not want longwinded instructions on how to run your macro. You may not even want them to be able to access the code. For maximum flexibility, you can use the CommandBars object to set up a menu, or you can place a command button on the spreadsheet itself.

3. Command Bars and Buttons 3 By using the CommandBars object, you can create new menus as part of the Microsoft Excel structure of menus; This effectively bypasses the ribbon, although it is still part of it. In Excel 2007, all custom menus are applied to a new item on the menu bar called Add-Ins. This has one ribbon control, called Menu Items, which is where all custom menu items are displayed.

4. Command Bars 4 The CommandBars object represents the Classic Excel spreadsheet menus and allows you to add your own menu commands into the Excel Add-Ins menu item. You can add a new menu group under the Add-Ins ribbon called MyMenu with a submenu item to call your procedure. This adds a very professional look to your application and, provided you include the means to remove the entry from the Add-Ins ribbon, the user will be impressed that your custom menu item forms part of the standard Excel menu system.

5. Command Bars 5 Here is a simple example that adds a new menu item under Tools and attaches some code to it. Insert a module and then add the following subroutine: Sub MyProc() MsgBox "You pressed my menu item" End Sub

6. Command Bars 6 Now add the following code: Sub AddMyMenu() Dim Cbar As CommandBar Define a command bar object pointer variable Dim CBarCtl As CommandBarControl Define a commandbar control object pointer variable Set Cbar = Application.CommandBars("Worksheet Menu Bar") Create the command bar and set it into Cbar pointer Set CBarCtl = Cbar.Controls.Add(Type:=msoControlPopup) Create an CommandBarControl Popup Object and add it to be an element of COmmandBar ponted by Bbar and point by CBarCtl With CBarCtl .Caption = "My Menu Give a caption to the BarController With .Controls.Add(Type:=msoControlButton) add a button type control object to The BarControl Object .Caption = "My Sub Procedure Set its Caption to My Sub Procedure .OnAction = "MyProc As On click action call the subroutine MyProc End With End With End Sub

7. Command Bars 7 Run the code only once and then go to the spreadsheet. If you run this code again, a second menu item called My Code will appear, which could be confusing. You will now see that there is an additional menu item on the menu bar called Add-Ins. Click this item and a single ribbon control called Menu Items will appear containing your new menu group called My Menu . Click MyMenu and you will see your submenu item called My Sub Procedure . Click My Sub Procedure and your message box will appear as defined in the subroutine MyProc.

8. Command Bars 8 The first line of the code adds the menu bar My Menu to the Excel menu Add-Ins item. The second line of code describes what action to take when the user does this. The OnAction property is set to point to the subroutine MyProc , which you just created.

9. Command Bars 9 If you exit Excel and load the application again, even without your file present (this is the file containing the code that you used to generate the new menu items), your menu item will still be there. It appears to be permanent, even if you click your new menu item without your original file loaded, Excel will load the excel document containing the VBA on-action code in it, so that your menu item still works.

10. Command Bars 10 How can you remove the menu entry? By using this Delete method: Sub RemoveMyMenu() Dim Cbar As CommandBar On Error Resume Next Set Cbar = CommandBars("Worksheet Menu Bar") Cbar.Controls("My Menu").Delete End Sub Run this code, and the Add-Ins item on the menu bar will vanish.

11. Command Bars 11 On Error Resume Next Specifies that when a run-time error occurs, control goes to the statement immediately following the statement where the error occurred, and execution continues from that point. Set Cbar = CommandBars("Worksheet Menu Bar") Cbar.Controls("My Menu").Delete The CommandBar named as Worksheet Menu Bar is selected and its control having the caption My Menu is deleted.

12. Command Bars 12 Menu items can be split into sections using a horizontal bar. You can add this line to command bars by using the BeginGroup method. The following code creates three menu items. You create the last item as a new group by setting the BeginGroup property for it to True.

13. Command Bars 13 Sub AddMyMenu2() Dim Cbar As CommandBar Dim CBarCtl As CommandBarControl Set Cbar = Application.CommandBars("Worksheet Menu Bar") Set CBarCtl = Cbar.Controls.Add(Type:=msoControlPopup) With CBarCtl .Caption = "My Menu" With .Controls.Add(Type:=msoControlButton) .Caption = "My Sub Procedure" .OnAction = "MyProc" End With

14. Command Bars 14 End With End Sub With .Controls.Add(Type:=msoControlButton) .Caption = "My Sub Procedure Again" .OnAction = "MyProc" End With With .Controls.Add(Type:=msoControlButton) .Caption = "A new group" .BeginGroup = True .OnAction = "MyProc" End With

15. Command Bars 15 You can also specify where on the menu list you want the item to appear. The default is to always appear at the bottom, but the before parameter allows you to specify where the item will be placed within the menu: Sub AddMyMenu3() Dim Cbar As CommandBar Dim CBarCtl As CommandBarControl Set Cbar = Application.CommandBars("Worksheet Menu Bar") Set CBarCtl = Cbar.Controls.Add(Type:=msoControlPopup) With CBarCtl .Caption = "My Menu"

16. Command Bars 16 With .Controls.Add(Type:=msoControlButton) .Caption = "My Proc" .OnAction = "MyProc" End With With .Controls.Add(Type:=msoControlButton) .Caption = "My Proc Again" .OnAction = "MyProc" End With With .Controls.Add(Type:=msoControlButton, before:=2) .Caption = "A new group" .BeginGroup = True .OnAction = "MyProc" End With End With End Sub

17. Command Bars 17 The before parameter is set to 2, which makes it the second item. Subsequent items in the menu bar will be moved down so that the existing menu item 2 will not be lost it now becomes menu item 3. You can also enable and disable your menu items by setting the Enabled property: CommandBars("Worksheet Menu Bar").Controls("My Menu").Enabled = False This will show your menu item grayed out, or disabled within the ribbon control. Setting it to True will enable it. You can also enable and disable the individual menu items within your main control My Code as follows: CommandBars("Worksheet Menu Bar").Controls("My Menu"). _ Controls("My Sub Procedure").Enabled = False

18. Command Bars 18 You can make your menu item invisible by setting the Visible property: CommandBars("Worksheet Menu Bar").Controls("My Code"). _ Controls("My Proc").Visible = False Your menu item will no longer be in the list, but it can be made visible again by setting the Visible property to True.

19. Command Buttons 19 You can insert controls directly onto the spreadsheet itself using the Control toolbox. Any control can be used, such as a drop-down list or a command button. Click the Developer item in the menu bar and then click the Insert icon in the Controls window of the ribbon. The Control toolbox will appear, just as it does on a UserForm within the VBA editor. Select the button (top left-hand control) from the Active X controls, not the User Form controls (this can be confusing, since these two groups appear on the same dialog).

20. Command Buttons 20 If you use the button in the User Form controls, you will not be able to switch it out of design mode and there is a danger that users will be able to modify it how they wish. You will also get a dialog asking you to assign a macro to the button, and this will show you that you have clicked the wrong control. Select the Command Button icon by clicking it and drag it onto the spreadsheet.

21. Command Buttons 21 Select the new button, right-click it, and then click the Developer item in the menu. Click View Code within the Controls window on the ribbon. This will take you into the code window, and the subroutine Button1_Click(). You can then place your code here or call another subroutine.

22. Command Buttons 22 You can change the text on the button by right-clicking the button and selecting Properties. Edit the Captionproperty to your requirements. When you complete your button, exit design mode by clicking the Developer item in the menu and the Design Mode icon in the Controls window of the ribbon. This toggles design mode on and off on the worksheet. When you click the button now, it is no longer in design mode and will run instead of having handles around it. Your button is now ready to run.

23. Command Buttons 23 You may wish to make further amendments to the button or even delete it completely if you are not happy with it. The only problem now is that every time you click it, it runs the code, and right-clicking does nothing because Excel VBA does not interpret the right mouse click on a command button! What you need to do is to put the Control toolbox back on screen by again clicking the Developer item in the menu and then clicking the Design Mode icon in the Controls window of the ribbon.

24. Command Buttons 24 You can now select the button, resize it, delete it, and so on. You can change the code behind the button without having to go into design mode. The code is completely separate from the spreadsheet itself. The code appears on the sheet object module for that particular sheet and can be easily edited.