Office365 MacOS Excel Error trying to add a worksheet change event

[brent3162]

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.

 

[Colin Legg]

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:

Private Sub Worksheet_Change(ByVal Target As Range)
    'put your code in here
End Sub

 

[brent3162]

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:

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.