Excel For Mac Vba Macros

Posted : admin | On 16-04-2021



I have a client who wants to use an Excel sheet with Macros on his Macbook Pro with OS X and Office 365 for Mac. When he opens the file, the buttons and dropdowns are converted to images. To help prevent macros that contain viruses from contaminating your system, by default Office for Mac displays a warning message whenever you try to open a document that contains a macro. This message appears regardless of whether the macro actually contains a virus.

I’m trying to get an Excel 2011 32-bit (for Mac) spreadsheet working that contains a macro. The problem is that this macro works fine on a PC, but not on the Mac. I tried to import Tim Hall’s Dictionary.cls, but it still doesn’t work. Use VBA add-ins and macros that you developed for Office for Windows with Office for Mac. Applies to: Excel for Mac PowerPoint for Mac Word for Mac Office 2016 for Mac. If you are authoring Macros for Office for Mac, you can use most of the same objects that are available in VBA for Office.

This Excel tutorial explains how to open the Visual Basic Editor in Excel 2011 for Mac (with screenshots and step-by-step instructions).

See solution in other versions of Excel:

You can access the VBA environment in Excel 2011 for Mac by opening the Visual Basic editor.

First, be sure that the Developer tab is visible in the toolbar in Excel.

The Developer tab is the toolbar that has the buttons to open the VBA editor and create Form Controls like buttons, checkboxes, etc.

To display the Developer tab, click on Preferences under the Excel menu at the top of the screen.

When the Excel Preferences window appears, click on the Ribbon icon in the Sharing and Privacy section.

In the Customize section, check Developer in the list of tabs to show. Then click on the OK button.

Select the Developer tab from the toolbar at the top of the screen. Then click on the Editor option in the Visual Basic group.

Now the Microsoft Visual Basic editor should appear and you can view your VBA code.

Written by Puneet for Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel for Mac

1. Run a Macro from the List

From the Developer Tab, you can access the list of the MACROS, which you have in your workbook or in PERSONAL.XLSB. To run a macro in Excel, you can use the below steps:

  1. Click on the macro button from the “Developer Tab” and open the list of macros.
  2. In this list of MACROS, you will have all the macro you have in the open workbooks, including the Personal Macro Workbook.
  3. Just select the macro you want to run and click on the “RUN” button.

When you click on the run button, it executes the macro and closes the dialog box.

2. Run a Macro with a Shortcut Key

You can also run a macro using a keyboard shortcut key. Usually, when you record a macro, it asks you to define a shortcut key that you can use to run that macro.

And if you are writing a macro, you can define a shortcut key from the list of macros.

  1. Select the name of the macro for which you want to define the shortcut key and click on the options.
  2. After that, click within the input box and press the shortcut key that you want to define.

3. Add a Macro Button to Quick Access Toolbar

You can also add a button to the Quick Access Toolbar to run a macro. You can use the below steps:

  1. First, click on the small dropdown that you have on the quick access toolbar and select more commands, and it will take you to the actual options to customize the quick access toolbar.
  2. Now from here, select the macros from the truth command from and select the macro that you want to add, after that click on the add button and it will add that macro to do quick access toolbar.
  3. In the end, click, OK.

And you will have a button for the macro that you have added.

4. Add Macro to a Shape

Let’s say you have a VBA code whichyou need to use frequently in your work. In this situation, you can create abutton and assign that macro to it.

  1. First, insert a simple shape from Insert Tab ➜ Illustrations ➜ Shapes. Select any of the shapes which you want to use as a button.
  2. After that, right-click on that shape and select “Assign Macro”.
  3. Now from the list of macros, select the macro which you want to assign to the shape.
File

Now, whenever you click on that shape, the macro which you assigned will execute.

5. Assign a Macro to a Form Control Button

Apart from using a shape, you can also use a control button to run a macro.

  1. First, go to the Developer tab and in the controls group and then click on insert. And from the insert drop-down, click on the button to insert it.
  2. After that, it will show you the macros list from where you can select it.
  3. Once you select the macro and click OK, you will get a button in the worksheet (you can change the text of the button to give it a meaningful name).

6. Opening and Closing a Workbook

You can also make a macro to run while opening and closing a workbook. That means when you open or close a workbook, the macro you have assign will get executed. For this, you need to use “auto_open” and “auto_close”.

Let’s suppose if you want to assign a macro to run while opening about the workbook. You need to use auto_open as the name of that macro.

Now, this micro will run when you open the workbook and enter the current date and type in the cell A1 of the active sheet.

In the same way, you can also use “auto_close” to make this macro while closing the workbook.

7. Activating and Deactivating a Worksheet

Excel Mac Vba Macro

Just like the workbook can also run a macro on activating and deactivating a worksheet. And in this case, you need to add that macro into the code window of that worksheet.

  1. First, right-click on the worksheet tab and click on the “view code”.
  2. Now in the code window, select the worksheet from the left drop-down. The moment you chose deactivate; you’ll get a new sub with the name “Worksheet_Deactivate”.
  3. Now you need to add the code in this procedure that you want to run when you deactivate the worksheet.
Excel For Mac Vba Macros

And if you want to run a macro when you activate a worksheet, select activate instead of deactivated from the drop-down.

8. Run a Macro When a Change in Worksheet

You can also run a macro when you make changes in a worksheet. For example, when you enter a value in a cell or delete a value from a cell.

For this, you, again, you need to enter the good in the code window of the worksheet and select “Selection Change” from the drop-down.

Macros

9. Within Another Procedure

Vba Macro Tutorial

You can run a macro from another procedure by using the call statement. Use the keyword Call and then the name of the macro.

When you run “markDone” macro, it first runs “mystrikethrough” macro and then makes the selection font bold.

10. Schedule a Macro

Excel For Mac Vba Macros Shortcut

You can also schedule a macro to run at a specific time. Let’s suppose if you want to run a macro in the morning at 8:30, you can specify the time, and we will run it.

For this, you need to use Application.OnTime method. Let’s suppose you have a macro “myCode”, you can write the code like below to run it at 8:30 AM.

If you liked this article, then please subscribe to our YouTube Channel for Video Tutorials.

VBA Tutorial Series

List Of Macros For Excel

VBA is one of the Advanced Excel Skills