XLSM Made in Excel 365 in Windows Removed Whenever Opened on Mac

[Abbaskip]

I hope this is the right forum - I'm assuming so, as it's only Macs where I'm having this issue.

I've made an XLSM file that works perfectly on my Windows PC running Excel for Microsoft 365 MSO (Version 2304 Build 16.0.16327.20324) 64-bit, but when people at my workplace with Macs try to open it they get:
1) An error stating "We found a problem with some content in filename.xlsm. Do you want us to try and recover as much as we can? If you trust the source of this workbook, click yes.
2) They click yes and the repair results show:
Repair Result to filename0.xml
Errors were detected in file ’filepath and filename.xlsm'

Removed Part: /xl/vbaProject.bin part. (Visual Basic for Applications (VBA))
As the vba project has been removed, obviously the macros don't work.
The macros are very simple (IMO) just copying values to a second sheet (using an Excel sheet as a form essentially):

Sub Rectangle1_Click()

ws_output = "Data"
next_row = Sheets(ws_output).Range("A" & Rows.Count).End(xlUp).Offset(1).Row
Sheets(ws_output).Cells(next_row, 1).Value = Application.WorksheetFunction.Substitute("IL-" & Range("Invoice_Date"), "/", "") & Right(WorksheetFunction.Text(next_row, "00"), 2)
Sheets(ws_output).Cells(next_row, 2).Value = Range("Invoice_Date").Value
Sheets(ws_output).Cells(next_row, 3).Value = Range("Due_Date").Value
Sheets(ws_output).Cells(next_row, 4).Value = Range("Chain").Value
Sheets(ws_output).Cells(next_row, 5).Value = Range("To").Value
Sheets(ws_output).Cells(next_row, 6).Value = Range("Address").Value
Sheets(ws_output).Cells(next_row, 7).Value = Range("Attn").Value
Sheets(ws_output).Cells(next_row, 8).Value = Range("Details").Value
Sheets(ws_output).Cells(next_row, 9).Value = Range("Details2").Value
Sheets(ws_output).Cells(next_row, 10).Value = Range("In_Contract").Value
Sheets(ws_output).Cells(next_row, 11).Value = Range("Amount").Value
Sheets(ws_output).Cells(next_row, 12).Value = Range("GST").Value
Sheets(ws_output).Cells(next_row, 13).Value = Range("Amount").Value + Range("GST").Value
Sheets(ws_output).Cells(next_row, 14).Value = Range("Paid_Date").Value
Sheets(ws_output).Cells(next_row, 15).Value = Range("FCM").Value
Sheets(ws_output).Cells(next_row, 16).Value = Range("CT").Value
Sheets(ws_output).Cells(next_row, 17).Value = Range("Stage").Value
Sheets(ws_output).Cells(next_row, 18).Value = Range("FCBT").Value
Sheets(ws_output).Cells(next_row, 19).Value = Range("FCM_ME").Value


End Sub


Is there something in this code that isn't Mac compatible? Is there an easy fix?

[Aussiebear]

Welcome to the VBAX Forum Abbaskip. There's nothing there that pops out as unworkable to me. Are you able to create an xlsx format and try to run that on a Mac. That will test is the standard file is an issue. If it accepts that then we need to look at the VBA side of things. I'm assuming that "Rectangle1" is a named range rather than a Shape?

[Abbaskip]

Welcome to the VBAX Forum Abbaskip. There's nothing there that pops out as unworkable to me. Are you able to create an xlsx format and try to run that on a Mac. That will test is the standard file is an issue. If it accepts that then we need to look at the VBA side of things. I'm assuming that "Rectangle1" is a named range rather than a Shape?

The xlsx opens and works completely on Macs once the file is opened and "repaired" so it definitely seems to be the vba

Rectangle1 is a shape - is that an issue?

I originally had a form button (not ActiveX), but changed to a rectangle shape in case the button was causing issues.

[Abbaskip]

Update: I've change the rectangles (and previously form buttons) to hyperlinks pointing to the cell that contains them and then used the sheet macros:


Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
If Target.Range.Address = "$E$29" Then
ws_output = "Data"
next_row = Sheets(ws_output).Range("A" & Rows.Count).End(xlUp).Offset(1).Row
Sheets(ws_output).Cells(next_row, 1).Value = Application.WorksheetFunction.Substitute("IL-" & Range("Invoice_Date"), "/", "") & Right(WorksheetFunction.Text(next_row, "00"), 2)
Sheets(ws_output).Cells(next_row, 2).Value = Range("Invoice_Date").Value
Sheets(ws_output).Cells(next_row, 3).Value = Range("Due_Date").Value
Sheets(ws_output).Cells(next_row, 4).Value = Range("Chain").Value
Sheets(ws_output).Cells(next_row, 5).Value = Range("To").Value
Sheets(ws_output).Cells(next_row, 6).Value = Range("Address").Value
Sheets(ws_output).Cells(next_row, 7).Value = Range("Attn").Value
Sheets(ws_output).Cells(next_row, 8).Value = Range("Details").Value
Sheets(ws_output).Cells(next_row, 9).Value = Range("Details2").Value
Sheets(ws_output).Cells(next_row, 10).Value = Range("In_Contract").Value
Sheets(ws_output).Cells(next_row, 11).Value = Range("Amount").Value
Sheets(ws_output).Cells(next_row, 12).Value = Range("GST").Value
Sheets(ws_output).Cells(next_row, 13).Value = Range("Amount").Value + Range("GST").Value
Sheets(ws_output).Cells(next_row, 14).Value = Range("Paid_Date").Value
Sheets(ws_output).Cells(next_row, 15).Value = Range("FCM").Value
Sheets(ws_output).Cells(next_row, 16).Value = Range("CT").Value
Sheets(ws_output).Cells(next_row, 17).Value = Range("Stage").Value
Sheets(ws_output).Cells(next_row, 18).Value = Range("FCBT").Value
Sheets(ws_output).Cells(next_row, 19).Value = Range("FCM_ME").Value
End If
End Sub




I've sent it to the people with Macs to see if this workaround works and will update.

[Abbaskip]

So the fix was to delete the MS Excel cache on all the Macs of those opening it. Not sure why they all had the issue - perhaps a work wide update or similar..

Procedures:
a. Close all Office 365 app for Mac applications.
b. From Finder Go/Go to Folder (Shift+Cmd+G).
c. Enter "~/Library/Containers" and press Go (Return key).
d. Find the "Microsoft Excel" folder (as well as Word, PowerPoint).
e. Move those folders to the desktop.
f. Restart the Mac to see the result.

[Aussiebear]

Thank you for posting your solution.