Command Button

[John Excel]

I have the following code in a spreadsheet but I get a run time error when it
reaches '(If Range("pxselect") > 0 Then)'.
Range 'pxselect' is a cell in the Customer Profile sheet. Please advise
where I am going wrong.


Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Customer Profile").Range("newused") = 1
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Dan").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
If Range("pxselect") > 0 Then
Sheets("Handback").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If

End Sub

Also, how can I get a pop up message asking "Are you sure you want to print"
when the command button is clicked.

Thanks in advance of a favourable reply.

John Davies

 

[JLGWhiz]

Just qualify the range with the sheet reference:

If Sheets("Customer Profile").Range("pxselect") > 0 Then

 

[RyanH]

Try this. Where is the name of the Worksheet that the Range("pxselect") is
in? I will assume Sheet1

If Sheets("Sheet1").Range("pxselect") > 0 Then

This is untested

Hope it helps!

 

[Jim Thomlinson]

As a guess the range pxselect is not on sheet Dan??? Assuming that to be the
case then we run into one of the odd things in VBA. Because of the defaults
If Range("pxselect") > 0 Then
is the same as
If Activesheet.Range("pxselect") > 0 Then
so if pxselect is not on the activesheet (Dan) then you are going to throw
an error even if pxselect is a global named range. The way around it is to
explicitly define the sheet where the named range exists...

If Sheets("Handback").Range("pxselect") > 0 Then 'as a guess

 

[JLGWhiz]

I don't want to be a nit picker, but it looks like you are
printing the same sheet twice with only a minor change in
Range("newused").Assuming that you have only one sheet
selected at a time, why not:

Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
Sheets("Customer Profile").PrintOut Copies:=1, Collate:=True
Sheets("Customer Profile").Range("newused") = 1
ActiveSheet.PrintOut Copies:=1, Collate:=True
Sheets("Dan").PrintOut Copies:=1, Collate:=True
If Sheets("Customer Profile").Range("pxselect") > 0 Then
Sheets("Handback")..PrintOut Copies:=1, Collate:=True
End If
Application.ScreenUpdating = True
End Sub

You also forgot to turn your screen updating back on.
Unless you are printing to a network printer and want
more than one copy, you don't really need the copies and
collate part of the print command.

 

[Jim Thomlinson]

It is not strictly necessary to turn screen updating back on. As a part of
housekeeping when all code has finished executing screenupdating will be
restored.
That being said I always turn screenupdating back on as a matter of
practice. It all goes back to cleaning up after yourself. It is never a
problem if you restore the application settings to theri original state. If
MS decides to change the way screenupdating works in the future and requires
you to turn it back on then your code is future proof. If you did not then
you have a lot of work ahead of you...

Also your psoted code has 2 dots where there should only be one...
Sheets("Handback")..PrintOut

 

[JLGWhiz]

I thought I got rid of that second dot dot. <g>

It is not strictly necessary to turn screen updating back on. As a part of
housekeeping when all code has finished executing screenupdating will be
restored.
That being said I always turn screenupdating back on as a matter of
practice. It all goes back to cleaning up after yourself. It is never a
problem if you restore the application settings to theri original state. If
MS decides to change the way screenupdating works in the future and requires
you to turn it back on then your code is future proof. If you did not then
you have a lot of work ahead of you...

Also your psoted code has 2 dots where there should only be one...
Sheets("Handback")..PrintOut