Application substitute for MAC

[musoguy]

Hi folks,

A workbook I have created has a macro that the user can select to print certain sheets. There is one line of code that works fine on a PC, but a couple of the users have MACs and the line of code doesn't work. I was wondering if anyone knows what to substitute it as for MAC (if there even is a substitute)

Application.Dialogs(xlDialogPrinterSetup).Show

Thanks in advance for any help,

James

 

[SydneyGeek]

Do you know which Office version they are using? The reason I ask, is that I think Application.Dialogs is part of VB6. VBA for Mac has been based on VB5 until Office 2004. I think the most recent version uses VB6.

Denis

 

[musoguy]

It's what ever the latest version on Office for Mac is. 2011 I think? I know very little about Macs! But it is the latest version of Office

 

[RoryA]

The closest you could use is:

Application.Dialogs(xlDialogPrint).Show

as far as I know.

 

[musoguy]

Thanks Rory, I'll add the code and get the Mac user to try it out. Love your signature quote by the way!

 

[mikerickson]

You could substitute this for that one line

#If Mac Then
    MsgBox MacScript(PrintSetupMacScript())
#Else
    Application.Dialogs(xlDialogPrinterSetup).Show
#End If

That code calls this function

Function PrintSetupMacScript() As String
    PrintSetupMacScript = "try"
    PrintSetupMacScript = PrintSetupMacScript & vbLf & "tell application ""System Preferences"" "
    PrintSetupMacScript = PrintSetupMacScript & vbLf & "    reveal pane ""Print & Fax"" "
    PrintSetupMacScript = PrintSetupMacScript & vbLf & "    activate"
    PrintSetupMacScript = PrintSetupMacScript & vbLf & "    repeat while (name of window 1) = ""Print & Fax"" "
    PrintSetupMacScript = PrintSetupMacScript & vbLf & "   end repeat"
    PrintSetupMacScript = PrintSetupMacScript & vbLf & "end tell"
    PrintSetupMacScript = PrintSetupMacScript & vbLf & "return true"
    PrintSetupMacScript = PrintSetupMacScript & vbLf & "on error"
    PrintSetupMacScript = PrintSetupMacScript & vbLf & "return false"
    PrintSetupMacScript = PrintSetupMacScript & vbLf & "end try"
End Function

 

[musoguy]

As I have no way to currently check this, would you recommend this version over the one line of code?

 

[mikerickson]

That was my first post in this thread.
What it does is open the printer preferences pane of the System Preferences.

As Rorya suggested, selecting the printer is "on top" of the Print dialog box on a Mac.

What functionality does the Printer Preferences on Windows Excel offer to the user?

 

[musoguy]

Thanks Mike, yeah I just realized that! It just allows you choose which Printer you want to print to, which is all I need to do.

 

[mikerickson]

In that case, that functionality is "on the top" of the normal Print screen for Mac.
"On top" both in the sense that no subordinate dialog needs to be invoked and it is above all the other options on the Print Dialog box.