vba - printing code

[ADK]

The code below works.

First question is when the dialog box appears for the user's input on which
printer
Application.Dialogs(xlDialogPrinterSetup).Show

If you click cancel, it still sends the print. Is there a way to cancel the
sub if the cancel button is clicked?

Second question is after the user clicks the PRINT command button and sends
the print, the button is still focused.
In code I only know to place Range("A7").Select to unfocus the button.
Without this line the button remains focused. If you press the Esc key it
unfocuses. I would like the result to be similar to the Esc key with having
to select a cell. Possible? How?


-----------------------------------------------------
Private Sub CompletionTablePrint_Click()

On Error GoTo addError

Application.Run "Module5.UnProtectPDSR"
Worksheets("CompletionTable").Activate
Application.Dialogs(xlDialogPrinterSetup).Show
Set c = Worksheets("CompletionTable").Columns("K"). _
Find("0", LookIn:=xlValues)
If Not c Is Nothing Then
Worksheets("CompletionTable").Range(Cells(1, 1), Cells(c.Row, 9)).Select
ActiveSheet.PageSetup.PrintArea = Selection.Address
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("A7").Select
Application.Run "Module5.ProtectPDSR"
End If

Application.Run "Module6.CompletionFilter"

Exit Sub

addError:
MacName = "CompletionTablePrint"
MyErrorRoutine Err.Number, Err.Description, MacName

End Sub

 

[Gary Keramidas]

maybe add something like this

dim bOK as string

bOK = Application.Dialogs(xlDialogPrinterSetup).Show 'choose the printer
If bOK = False Then exit sub

 

[Dave Peterson]

What happens if the correct printer is already chosen and the user clicks cancel
because there's no need to change it?

You may just want to ask directly:

dim resp as long
resp = msgbox(Prompt:="wanna print", buttons:=vbyesno)
if resp = vbno then
exit sub '???
end if

But if you want, you could use something like:

Dim res As Boolean
res = Application.Dialogs(xlDialogPrinterSetup).Show
If res = True Then
MsgBox "ok was clicked"
Else
MsgBox "cancel was clicked"
End If

=======
And I'd try two things...

Rightclick on that commandbutton from the control toolbox toolbar (while in
design mode) and choose properties. Change the .takefocusonclick to false.

If that doesn't work (it worked fine for me), then add
activecell.activate
and let the user go back to the cell that was active before.

 

[ADK]

This helped ...thanks for that ...How about Question 2?

 

[ADK]

This dialog has an OK button, so if the printer is correct and they want to
print, they just click OK

Gary's post answered the cancel issue but still looking for help with
question 2

Thanks for your reply

 

[Dave Peterson]

As a user, I'll often use the cancel button if I don't need to change anything.

Reread my response for a couple of suggestions for #2.