Folioblog272

1. Edit A Button In Excel
2. Excel Button To Run Macro
3. Editing Macros In Excel 2010
4. Excel Assign Macro To Button

If your work requires navigating between worksheets a lot, activating those sheets can quickly become a hassle. Some VBA tips we’re going to cover here can help you to create a popup list of worksheets where you want them, and save yourself from the default worksheets menu. In this article, we are going to show you how to create a worksheet selection popup in Excel.

Before starting

Click the Edit button. The macro code appears in the VBA editor. Change the name that appears in the Sub line from Button1Click to RenamedMacro. Leave Sub and parenthesis as they are. Open the Macro dialog box. The macro name appears as you renamed. Click RenamedMacro. Click the Run button. Now a button click is not necessary. The second arrow highlights the Visual Basic and Macros buttons mentioned earlier. The visual basic button will simply open the VBA window and display code (also can use shortcut Alt + F11) but to display the code of the macro that was just recorded or to access all macros click on the Macros button. You can use the Macro dialog to open the code for a macro by selecting the macro’s name and clicking the Edit button. This will open the macro in the VB Editor. Alternatively, you can open the VB Editor directly by clicking the Visual Basic button on the Developer tab,. INSTAGRAM: 𝐋𝐢𝐧𝐤: 𝐋𝐢𝐧𝐤: https://www.pcgamingrace.com. The form control button (push button) is assigned to run a macro. You can right click on the button to edit the text or to format it. This is Excel 2010 screenshot.

If you are new to VBA and macros – in a nutshell, VBA is a programming language for Office products. Microsoft allows users to automate tasks or modified properties of Office software. A macro, on the other hand is a set of VBA code, defining a calculation process.

Macros, or codes, should be written in modules, which are text areas in VBA’s dedicated user interface. Also, the file should be saved as an Excel Macro Enabled Workbook in XLSM format to keep the code.

You can find detailed instructions in our How to create a macro in Excel guide.

Creating a worksheet selection popup by VBA

The following code will display a worksheet selection popup at where the mouse cursor is.

Sub DisplayWorksheetPopup()

Application.CommandBars(“Workbook Tabs”).ShowPopup

End Sub

If you run the macro right away, you will see a list of your sheets names in a popup list. Clicking on a name navigate you to that sheet.

You can either bind this to macro to a button or shortcut to run without the VBA screen.

Binding to a button

You can add a button in your worksheet from Developer tab of the ribbon. If you are not familiar with inserting controls, you can follow the steps below:

1. Activate the Developer tab in the ribbon
2. Press the Insert icon to see the controls
3. Click on Button in Form Control section
   
4. Click on your worksheet where you want to place the button.
5. Assign Macro dialog will pop up
6. Select the macro you want run when you click the button
7. Click OK to create the button
   

Once created, you can click on the button to display worksheet selection popup.

Using a shortcut to create a worksheet selection popup

You can assign keyboard shortcuts to your custom macro as well. First you need to open the Macro window and edit the macro.

1. Open the Macro window by clicking Macros icon in Developer
2. Select your macro
3. Click the Options button to open Macro Options dialog
4. While the small textbox is active, type a character you want as a shortcut
5. Click OK to apply the changes
   

Tip: All you need to is to click a single key. Excel offers either Ctrl + your key or Ctrl + Shift + your key combination based on available (not used) shortcut. For example, if you press K, Excel gives you Ctrl + Shift + K due Ctrl + K is existed.

Setting the popup location

If you are OK with the location based on where your mouse is pointing you can use the code provided as is. On the other hand, you can also set a specific location by specifying the coordinates.

You need to determine pixel coordinates after ShowPopup command. Excel counts pixels by starting from the top left. For example, to open at the target 500px left and 250px below the top left cell, use the following code:

Sub DisplayWorksheetPopup()

Application.CommandBars(“Workbook Tabs”).ShowPopup 500, 250

End Sub

A versatile code

The worksheet selection popup has a 16-items limit. If you have more than 16 sheets, you will see 15 of the worksheets and a More Sheets item which opens the Activate window. The Activate window is a dialog that lists all worksheets.

As a result, there are 2 similar windows. By the following code you can call either one according the number of sheets in your workbook:

Sub VersatileDisplayWorksheetPopup()

If ActiveWorkbook.Sheets.Count > 16 Then

Application.CommandBars(“Workbook Tabs”).Controls(“More Sheets…”).Execute

Else

Application.CommandBars(“Workbook Tabs”).ShowPopup

End If

End Sub

Editing a Macro

If you need to make simple changes to a macro, such as inserting text or deleting a command, such as a specific format applied to a cell, you can edit the macro. You edit a macro in the Visual Basic Editor, shown in Figure 2-6. The elements of the Visual Basic Editor are described in the table below.

The Project Explorer, Properties window, and Code window all appear when you open the Visual Basic Editor. Since you won’t need the Properties window while performing simple editing, you can close the Properties window, and then expand the Project Explorer to view more of its window.

Edit A Button In Excel

Each open workbook in Excel has a project associated with it in the Project Explorer. Navigating the Project Explorer is similar to navigating Windows Explorer, in that they both have hierarchical structures. The code for a macro is stored in a module, which is simply a holding place for the code, just as a worksheet is a holding place for data in cells. Double-clicking a module in Project Explorer displays the module’s code in the Code window. Editing Visual Basic code is similar to editing text in a word processing program.

For more details of Macro and VBA Excel training in Los Angeles call us on 888.815.0604. Our classes are hands-on and instructor-led. Beginner, Intermediate and Advanced Excel classes are also available.

Figure 2-6: The Visual Basic Editor

Steps to Edit a Macro

To display the Visual Basic Editor:

1. In the Code group on the Developer tab, click the Visual Basic button.

To edit a macro:

1. Display the Visual Basic Editor.
2. From the Tools menu, choose Macros.
3. In the Macros dialog box, from the Macros In drop-down list, select the project containing the macro you want to edit.
4. In the Macro Name list box, select the desired macro.
5. Choose Edit.
6. In the Code window, make the desired edits.
7. Close the Macros dialog box.

To close the Visual Basic Editor:

Excel Button To Run Macro

1. From the File menu, choose Close and Return to Microsoft Excel.

Also see how to record a Macro.

Deleting a Macro

If you no longer need a macro, you can delete it. Deleting unwanted macros makes it easier to view macros in the Code window in the Visual Basic Editor as well as view macros in the Macro dialog box.

You can delete a macro in an open workbook using the Macro dialog box or the Visual Basic Editor. If you want to delete a macro in the Personal Macro Workbook using the Macro dialog box, you must first unhide the Personal Macro Workbook. A benefit of using the Visual Basic Editor is that you can delete any macro in any open workbook or the Personal Macro Workbook, without unhiding it.

Steps to Delete a Macro

Editing Macros In Excel 2010

Macro dialog box method:

1. Locate the Code group in the Developer tab on the Ribbon
2. In the Code group on the Developer tab, click the Macros button.
3. In the Macro dialog box, in the Macro Name list box, select the macro you want to delete.
4. Choose Delete.
5. In the message box that appears, choose Yes.

Excel Assign Macro To Button

Visual Basic Editor method:

1. Locate the Code group in the Developer tab on the Ribbon.
2. In the Code group on the Developer tab, click the Visual Basic button.
3. From the Tools menu, choose Macros.
4. In the Macros dialog box, from the Macros In drop-down list, select the project containing the macro you want to delete.
5. In the Macro Name list box, select the desired macro.
6. Choose Delete.
7. From the File menu, choose Close and Return to Microsoft Excel.