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

maybe add something like this

dim bOK as string

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

--


Gary


"ADK" wrote in message
...
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

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


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
maybe add something like this

dim bOK as string

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

--


Gary


"ADK" wrote in message
...
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

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 wrote:

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

--

Dave Peterson

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" wrote in message
...
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 wrote:

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

--

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.

ADK wrote:

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" wrote in message
...
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 wrote:

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

--

Dave Peterson

--

Dave Peterson