Author: Debra Dalgleish

As part of the Excel programming series, I described how to run an Excel macro by adding a button to the Quick Access Toolbar (QAT).

Video: Add Macro Button to QAT

If you use a macro frequently, you can add its icon to the Quick Access Toolbar (QAT). There are written instructions below the video

Add Macro Button to QAT

In this example, there is a macro named ToggleR1C1. that I use frequently. The macro toggles the worksheet reference style from letters (A1 style) to numbers (R1C1 style)

The ToggleR1C1 macro is stored in my Personal Macro Workbook, which is named Personal.xlsb.

• Tip: Further down this page, there’s a video that shows how I recorded and edited the ToggleR1C1 macro

To add that macro to the QAT, I followed the steps below.

• First, at the right end of the QAT, click the drop down arrow
• Click More Commands

Excel Options Window

When the Excel Options window opens, the Customize category, at the left, is automatically selected.

• In the window pane at the right, near the top, click the drop down arrow for the “Choose commands from” setting
• In the drop down list, click on Macros

Select a Macro

• Next, in the list of macros, click the PERSONAL.XLSB!ToggleR1C1 macro
  • PERSONAL.XLSB is the file name for the Personal Macro Workbook
• Then click the Add button, to move the selected macro to the Quick Access Toolbar

Choose Macro Button Icon

Next, follow these steps to add a picture (icon) to the Macro button

• At the right, in the QAT list, click the PERSONAL.XLSB!ToggleR1C1 macro
• Next, below the list, click the Modify button
• When the Modify Button dialog box opens, look for an icon will remind you of what your macro does.
• Next, click on the icon you want to use for the macro
  • I used the 8-ball, because the macro switches headings from letters to numbers, or vice versa.
• Then, click the OK button, to close the Modify Button dialog box
• And finally, click the OK button, to close the Excel Options window

Use the Macro Button

When you’re back in Excel, the macro icon now appears on the QAT.

When you want to run your macro, just click its icon on the QAT.

New I can I click the 8 Ball icon, when I want to toggle the worksheet reference style from letters (A1 style) to numbers (R1C1 style)

Video: Record & Edit Macro to Toggle Styles

Watch this video, to see how to record your own macro, to change between R1C1 style (column numbers) and A1 style (column letters). Then, add a button for the macro on the Quick Access Toolbar.

Video: Add Excel Options to QAT

If you use the Excel Options window frequently, you can add a command to the Quick Access Toolbar, to open that window quickly.

In this video, I show how to turn off the Excel Option Boxes, with a setting in the Excel Options window. Next, I show how to add an Options command to the QAT, so the window opens with a specific tab activated

More Quick Access Toolbar Tips

Visit my Contextures site for more tips for using the Quick Access Toolbar with your macros, such as adding QAT buttons that run macros for a specific workbook only.

___________

In the Excel VBA Edit a Recorded Macro article, you saw the steps for making changes to an Excel macro that you had recorded.

The original range was 50 rows, and I increased both references, to make them each 500 rows.

Video: Edit & Test Excel Macro

I’ve uploaded a new video that shows the steps for editing the Excel macro, and stepping through the macro code by using the F8 key.

The full transcript is below the video

Video Transcript

I recorded a macro in Excel and now we’d like to make a little change to that macro and test it.

Here’s the workbook where I store the macro

And to get started, there’s a security warning here.

I’m going to click Options and click, Enable this content, and OK

Now the macros will run.

See Macro Code

To see that macro, I’ll go to the Developer tab, click Macros

Select my macro in the list, and click Edit

That opens the Visual Basic Editor, and I can see all the workbooks that are open.

Most of these are hidden and just working in the background, but here’s the workbook where I recorded and stored the code.

And those are the sheets, and here’s the module where the code is stored

See Recorded Macro Steps

In this macro, I can see all the steps that were recorded.

• It opens a workbook
• Goes to a sheet
• Range A1 on that sheet
• Turns on an AutoFilter
• Filters for Binders
• Selects range A1 to J50
• Copies it
• Adds a new workbook
• And pastes what we copied

The change I want to make is just making this a bit bigger range.

• Right now, it’s going to row 50, which is where the orders stop now
• I know that I’ll be adding more orders every month, so I’m going to change the 50 to 500

Change the Code and Save

I’ll change it there, and in the next line of code, that also had a 50

I’ve made the change, and to save that, I’ll go up to the toolbar, and click the Save button

Test Revised Macro

Now, to test this revised macro:

• I’m going to just click somewhere between the Sub, and the End Sub row
• I’ll click here, and on the toolbar, I’ll click Debug
• There’s a Step Into command, and the keyboard shortcut is F8
• I’ll click Step Into, and it highlights the first row in the macro
• I’ll press F8 on the keyboard, and that takes me down to the first line of code, which is Workbooks.Open

It’s highlighting the row in the code that it’s about to run

• When I press F8, it opens that workbook, and moves down to the next line of code

Here’s the workbook in the background now

• And it will go to the Data sheet
• I press F8, and it was already on the Data sheet, so nothing happened
• Then it’ll go to range A1, and turn on the AutoFilter
• I press F8 again, and you can see the filter arrow there now

The next line of code tells it to filter for Binder orders

• So F8, and now you can see the row numbers are blue, and some rows are hidden, so the filter has been applied
• Now it will select A1 to J500, when I press F8
• Then it copies, and F8 again, to add a new workbook

And you can see the blank sheet in the background now

• And now it will paste when, I press F8
• And I’m at the last row here, so when I press F8 again, it’ll just take me out of the macro

We have made a revision to the macro, we saved it, tested the macro, and it works fine.

We can just close our macro workbook now, and know that our revised macro works fine

____________