Consulting

Results 1 to 6 of 6

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

  1. #1
    VBAX Newbie
    Joined
    Jun 2023
    Posts
    4
    Location

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

    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?

  2. #2
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,217
    Location
    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?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  3. #3
    VBAX Newbie
    Joined
    Jun 2023
    Posts
    4
    Location
    Quote Originally Posted by Aussiebear View Post
    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.

  4. #4
    VBAX Newbie
    Joined
    Jun 2023
    Posts
    4
    Location
    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.

  5. #5
    VBAX Newbie
    Joined
    Jun 2023
    Posts
    4
    Location
    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.
    Last edited by Aussiebear; 06-22-2023 at 01:36 AM. Reason: Edited the presentation

  6. #6
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,217
    Location
    Thank you for posting your solution.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •