Office365 MacOS Excel Error trying to add a worksheet change event

brent3162

New Member
Joined
Jul 13, 2024
Messages
11
Office Version
  1. 365
Platform
  1. MacOS
Has anyone figured out how to get around this issue that seems to be specific to MacOS Excel 365?

When I'm in the visual basic editor, I select the sheet I want to monitor for a change, I attempt to select the "General" drop down selector, just above the code window and choose "Worksheet" so that I can enter the change event code but I get an error message. See attached

what I have is a customer name drop down list and when I select the customer from the customers worksheet, I want the automatically add the street address on next row below name and then city,state and zip on row below that. But cannot get to adding the code due to the error.
 

Attachments

  • Excel for MACOS error.gif
    Excel for MACOS error.gif
    177.1 KB · Views: 16

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
I've never used MacOS 365 (so I can't offer advice based on experience) but, according to this Microsoft Support article, this error is a known issue with the Mac Visual Basic Editor. The VBA code, once it is entered, should work - you just can't use the editor to create the procedure stub. The article provides some workarounds such as drafting up the code on Windows (which is pretty ridiculous) and also provides the signatures of the workbook and worksheet events. I think the one you're after is:

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    'put your code in here
End Sub
 
Upvote 0
I've never used MacOS 365 (so I can't offer advice based on experience) but, according to this Microsoft Support article, this error is a known issue with the Mac Visual Basic Editor. The VBA code, once it is entered, should work - you just can't use the editor to create the procedure stub. The article provides some workarounds such as drafting up the code on Windows (which is pretty ridiculous) and also provides the signatures of the workbook and worksheet events. I think the one you're after is:

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    'put your code in here
End Sub
Yes thanks @Colin Legg I had read that article and tried some of the workarounds but still could not get it to work on the MacOS Excel 365 version running locally. I was not going to go find a Windows machine, use a license key, then save it and come back to MacOS. Ridiculous workaround for sure. But.....

Interestingly, just today, I found that I could RIGHT CLICK on the worksheet tab for which I wanted the event to occur and paste in the change event code very similar to what you show above. When I pasted it in, then selection automatically went to the Worksheet setting with Change as the setting. So, I have found other things like this where the MacOS developers at Microsoft or wherever they are, try to do things automatically (similar to other MacOS automations) in the theme of making things easier for Mac users.

If you have this issue and all you see is General when you try to drop down the selector within the IDE code window, change your approach. Right click the worksheet tab for which you want the action to occur and select "View Code" then paste in your worksheet change code. Boom, it will get set automatically for you.
 
Upvote 0
Solution

Forum statistics

Threads
1,223,886
Messages
6,175,191
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top