can this code work on mac excel?

elwood96

New Member
Joined
Jun 12, 2008
Messages
9
Hi there,

I am wondering if there is anyway to make the following code work on both PC and mac? It works fine on PC, but on mac the BeforeSave and BeforePrint code does not work and the SaveASheet code gets as far as opening and pasting the sheet but then is unable to save automatically.

thanks

Private Sub Workbook_BeforeClose(Cancel As Boolean)
SaveASheet
End Sub
Private Sub Workbook_Open()
Sheets("Open").Activate
End Sub
Sub SaveASheet()
Dim fName As String
Dim myPath As String
Dim sht As Worksheet
#If Mac Then
myPath = "file://localhost/Volumes/DOCUMENTS/3. Design/Supply Orders/Log/"
#Else
myPath = "U:\3. Design\Supply Orders\Log\"
#End If
For Each sht In ThisWorkbook.Worksheets
If (sht.Range("D1").Value <> "") * (sht.Range("g11").Value <> "") Then
fName = myPath & sht.Range("g11").Value & ".xls"
sht.Copy
With ActiveWorkbook
.SaveAs fName
.Close False
End With
End If
Next sht
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI = True Then
MsgBox "Please use 'Save' to Save this Workbook...", vbCritical, "Your title"
Cancel = True
End If
End Sub
Private Sub Workbook_BeforePrint(Cancel As Boolean)
If Range("f13").Value = "Update Register" Then
Cancel = True
MsgBox "You must complete the register before printing"
End If
End Sub
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
I don't understaand why those guys create a new thread without replying to their original thread....
Mac path separator is different from Windows.

Application.PathSeparator
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Which version of Excel on the Mac?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

There is no version 2007 of Excel for Mac. The two latest ones are 2004 and 2008. 2008 does not support VBA at all so it must be 2004 or earlier. I just checked 2004 and it does support both of those events and your BeforePrint event worked just fine.
 

elwood96

New Member
Joined
Jun 12, 2008
Messages
9
thanks for your help rorya and jindon. if 2008 came out this year, then we most certainly will be on 2004 or earlier. I will try the code again soon. cheers.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

I notice you are using a conditional compilation constant 'Mac' in the code. Have you changed this when the workbook is running on the Mac?
 

elwood96

New Member
Joined
Jun 12, 2008
Messages
9
no. when opened on a mac i used the exact code as above. does this 'Mac' part need to be removed?
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,985
Its probably 2004. 2007 is for Windows and Excel 2008 does not support macros.

2004 is awfully buggy when it comes to ThisWorkbook events, but the Before_Save and BeforePrint code looks OK (but you may want to qualify Range("f13") in to ActiveSheet.Range("f13"))

As Jindon mentioned, the path seperator is different in Mac. Putting =CELL("filename") in a cell will give you an idea of what the variable myPath should look like.
(My current test file's filepath is "Macintosh HD:Users:merickson:Desktop:[Workbook1.xls]Sheet1")

from the VBE help system:
"Strategies for Developing Cross-Platform Solutions

Use Built-in Properties for Paths
In applications whose object models support built-in properties, use these properties to return a file or folder name. For example:
• In Word, Excel, and PowerPoint, use the Path property of the Application object to return the path to the application.
• In Word and Excel, use the PathSeparator property of the Application object rather than a backslash or colon to separate folder names in a path.
• In Word, use the DefaultFilePath property of the Options object to return a folder, such as the Templates folder."

PS. Application.OperatingSystem Like "*Macintosh*" is a good test for which kind of system you are on.
 
Last edited:

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,985
If the workbook in which that code resides is in the same folder that you want to write the sheets to

Code:
myPath = ThisWorkbook.Path
would be one way to make that part of the code cross-platform.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,119
Messages
5,640,219
Members
417,131
Latest member
Seanr19871

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
Top