I've written a small macro that once it's run I want the user to print
a copy of the worksheet and then close the worksheet. Currently I'm
performing

Excel.Application.ThisWorkbook.PrintOut
Excel.Application.ThisWorkbook.Close

Is there a way I can display a printer select dialogue AND ensure that
the worksheet has at least been spooled to the printer?

Also when I perform my .Close it always asks the user if they'd like to
save any changes. I've already performed a .SaveCopyAs to archive what
the user has done I don't want them to have the ability to save changes
to my template file. Granted my template file is READ ONLY and they
can't save changes to it but I'd still like them not to be prompted to
save changes on a .Close. Is there any way to .Close without prompting
the user to save changes?

Thanks
Eric

Not sure what you want with the print dialog box, but to close without saving:

ActiveWorkbook.Close savechanges:=False

This will display the print dialog:

Application.Dialogs(xlDialogPrint).Show

Put your cursor on Dialogs and hit F1 to get the help topic. You will find a
link to all of the built in arguments.

"Wescotte" wrote:

I've written a small macro that once it's run I want the user to print
a copy of the worksheet and then close the worksheet. Currently I'm
performing

Excel.Application.ThisWorkbook.PrintOut
Excel.Application.ThisWorkbook.Close

Is there a way I can display a printer select dialogue AND ensure that
the worksheet has at least been spooled to the printer?

Also when I perform my .Close it always asks the user if they'd like to
save any changes. I've already performed a .SaveCopyAs to archive what
the user has done I don't want them to have the ability to save changes
to my template file. Granted my template file is READ ONLY and they
can't save changes to it but I'd still like them not to be prompted to
save changes on a .Close. Is there any way to .Close without prompting
the user to save changes?

Thanks
Eric

I'm looking to display the print dialog and only return true if the
user clicked OK
so I have the following. The function below works but I'd also like to
force them to print the entire worksheet and not have any other optiosn
but selecting the printer to use. I've looked up the various arguements
for .Show but I can't see to get any to work. I've tried .Show
argname:= value but that didn't work.. I'm not sure what I'm doing
wrong

Function PrintSheet()

dlgAnswer = Excel.Application.Dialogs(xlDialogPrint).Show
If dlgAnser < -1 Then
PrintSheet = false
else
PrintSheet = True
End If
Exit Function

Try setting the print area. Some people use a Before_Print event to make sure
the proper range is set.

"Wescotte" wrote:

I'm looking to display the print dialog and only return true if the
user clicked OK
so I have the following. The function below works but I'd also like to
force them to print the entire worksheet and not have any other optiosn
but selecting the printer to use. I've looked up the various arguements
for .Show but I can't see to get any to work. I've tried .Show
argname:= value but that didn't work.. I'm not sure what I'm doing
wrong

Function PrintSheet()

dlgAnswer = Excel.Application.Dialogs(xlDialogPrint).Show
If dlgAnser < -1 Then
PrintSheet = false
else
PrintSheet = True
End If
Exit Function