Error Creating Event Handlers

Written by Allen Wyatt (last updated June 10, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365


When Steve tries to create an event handler, either for a workbook or for a worksheet, he gets an error message: "Variable Uses an Automation Type not Supported in Visual Basic." Excel won't allow the creation of any event handlers, even if Steve saves the workbook in XLSM format. He wonders what causes this error and how he can create the event handlers he needs?

It is not clear from the question which version of Excel Steve is using, but if he is using on a Mac platform, this problem has been known for some time. There is information about the problem here, on this web page:

https://support.microsoft.com/en-us/topic/-c3125bf3-c30b-4d62-be42-d2f1bec0e5ad

It is amazing that Microsoft has known about this problem for at least six years (since 2017) and it still has not been fixed. The web page provides some workarounds for the issue, as well. (And one jaw-dropping workaround is to create your event handler using Excel on a Windows system, and then transfer it to the Mac.)

Head-shaking workarounds aside, it is possible that Steve is experiencing the error on a Windows-based system. If this is the case, then the most likely issue is a problem with a variable defined in some pre-existing event handler or macro already on the system. The way to see if this is the cause is to get out of Excel, rename the personal macro workbook, restart Excel, and see if the problem exists with a new, blank workbook. If it goes away under those conditions, then you know you have some detective work to do in order to track down the problem in one of the macros in the personal macro workbook.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13374) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Can't Select Style Instances

Using the Styles and Formatting task pane, Word allows you to select all instances of a given style in your document. ...

Discover More

Copying Headers and Footers

Need to copy headers and footers from one worksheet to another? How about from one workbook to another? Here are some ...

Discover More

Specifying Paper Trays for Specific Pages in a Single Print Job

If your printer has multiple paper trays, you may want to send some pages to one tray and other pages to a different ...

Discover More

Best-Selling VBA Tutorial for Beginners Take your Excel knowledge to the next level. With a little background in VBA programming, you can go well beyond basic spreadsheets and functions. Use macros to reduce errors, save time, and integrate with other Microsoft applications. Fully updated for the latest version of Office 365. Check out Microsoft 365 Excel VBA Programming For Dummies today!

More ExcelTips (ribbon)

Finding the Last-Used Cell in a Macro

Ever wonder what the macro-oriented equivalent of pressing Ctrl+End is? Here's the code and some caveats on using it.

Discover More

DOS from Macros

Need to run a DOS command from within one of your macros? The answer is the Shell command, described in this tip.

Discover More

Deleting Every X Rows

Grab some info from a source other than Excel, and you may find the need to delete a certain pattern of rows from a ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is eight more than 9?

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.