This code does not execute when the user clicks on the Print button. The code objective is stated below. One comment made is that in the select line, lower case is specified, where proper case is used in the case line. I changed the text in the case lines to lower case but the code still doesn't work. On thing: this workbook is a template used by many users. The worksheet tabs are typed in just as they appear in the code - Proper case. Does anyone have an idea to fix this code


The object of this code is to reset the scaling and print area in Page Setup €“ before printing begins. It is located in Module2 of the workbook. To test the code, I set the scale at 50% for the worksheets and clicked on the print button. It prints at 50% instead of 95%/90%, as proposed in the code. Its as though Excel doesnt see the code at all. Can someone look through this as suggest a fix? I can compress the file and send it if necessary

Private Sub Workbook_BeforePrint(Cancel As Boolean
Dim wsSheet As Workshee
Dim rng As Range, ar As Rang
Dim lngZ As Lon
For Each wsSheet In ActiveWindow.SelectedSheet
Select Case LCase(wsSheet.Name
Case "Scorecard
lngZ = 9
With wsShee
Set rng = .Range("B1:BA45"
End Wit
Case "Customer", "Financial", "Learning and Growth", "Internal Business Process
lngZ = 9
With wsShee
Set rng = .Range("B1:BA32,B33:BA64,B65:BA96"
End Wit
Exit Su
Case Els
With wsSheet.PageSetu
.FitToPagesWide =
.FitToPagesTall =
End Wit
Exit Su
End Selec
With wsSheet.PageSetu
.Zoom = lng
End Wit
Cancel = Tru
On Error GoTo ErrHandle
Application.EnableEvents = Fals
For Each ar In rn
ar.PrintOu
Nex
Nex
ErrHandler
Application.EnableEvents = Tru
End Su

It has to be located in the ThisWorkbok module as well, not a standard code
module.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Phil Hageman" wrote in message
...
This code does not execute when the user clicks on the Print button. The
code objective is stated below. One comment made is that in the select
line, lower case is specified, where proper case is used in the case line.
I changed the text in the case lines to lower case but the code still
doesn't work. On thing: this workbook is a template used by many users.
The worksheet tabs are typed in just as they appear in the code - Proper
case. Does anyone have an idea to fix this code?



The object of this code is to reset the scaling and print area in Page
Setup - before printing begins. It is located in Module2 of the workbook.
To test the code, I set the scale at 50% for the worksheets and clicked on
the print button. It prints at 50% instead of 95%/90%, as proposed in the
code. It's as though Excel doesn't see the code at all. Can someone look
through this as suggest a fix? I can compress the file and send it if
necessary.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wsSheet As Worksheet
Dim rng As Range, ar As Range
Dim lngZ As Long
For Each wsSheet In ActiveWindow.SelectedSheets
Select Case LCase(wsSheet.Name)
Case "Scorecard"
lngZ = 95
With wsSheet
Set rng = .Range("B1:BA45")
End With
Case "Customer", "Financial", "Learning and Growth", "Internal
Business Process"
lngZ = 90
With wsSheet
Set rng = .Range("B1:BA32,B33:BA64,B65:BA96")
End With
Exit Sub
Case Else
With wsSheet.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
Exit Sub
End Select
With wsSheet.PageSetup
.Zoom = lngZ
End With
Cancel = True
On Error GoTo ErrHandler
Application.EnableEvents = False
For Each ar In rng
ar.PrintOut
Next
Next
ErrHandler:
Application.EnableEvents = True
End Sub

First, these kind of events don't belong in general modules (like Module2).

This one is a workbook event. Therefore, it should be located behind the
ThisWorkbook object.

I didn't test your code, but after you move it there (delete the version in
Module2), you still have a couple of typos.

For instance:
Select Case LCase(wsSheet.Name)
Case "Scorecard"

You say you want to look at the lower case name of the worksheet. But you
compare it to "Scorecard" with an uppercase S. It'll never match (well, unless
you have "option compare text" at the top of the module--but if you did, you
wouldn't need the LCase() stuff at all).



Phil Hageman wrote:

This code does not execute when the user clicks on the Print button. The code objective is stated below. One comment made is that in the select line, lower case is specified, where proper case is used in the case line. I changed the text in the case lines to lower case but the code still doesn't work. On thing: this workbook is a template used by many users. The worksheet tabs are typed in just as they appear in the code - Proper case. Does anyone have an idea to fix this code?

The object of this code is to reset the scaling and print area in Page Setup €“ before printing begins. It is located in Module2 of the workbook. To test the code, I set the scale at 50% for the worksheets and clicked on the print button. It prints at 50% instead of 95%/90%, as proposed in the code. Its as though Excel doesnt see the code at all. Can someone look through this as suggest a fix? I can compress the file and send it if necessary.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wsSheet As Worksheet
Dim rng As Range, ar As Range
Dim lngZ As Long
For Each wsSheet In ActiveWindow.SelectedSheets
Select Case LCase(wsSheet.Name)
Case "Scorecard"
lngZ = 95
With wsSheet
Set rng = .Range("B1:BA45")
End With
Case "Customer", "Financial", "Learning and Growth", "Internal Business Process"
lngZ = 90
With wsSheet
Set rng = .Range("B1:BA32,B33:BA64,B65:BA96")
End With
Exit Sub
Case Else
With wsSheet.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
Exit Sub
End Select
With wsSheet.PageSetup
.Zoom = lngZ
End With
Cancel = True
On Error GoTo ErrHandler
Application.EnableEvents = False
For Each ar In rng
ar.PrintOut
Next
Next
ErrHandler:
Application.EnableEvents = True
End Sub

--

Dave Peterson

And for your reading pleasu

Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

and

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm


Dave Peterson wrote:

First, these kind of events don't belong in general modules (like Module2).

This one is a workbook event. Therefore, it should be located behind the
ThisWorkbook object.

I didn't test your code, but after you move it there (delete the version in
Module2), you still have a couple of typos.

For instance:
Select Case LCase(wsSheet.Name)
Case "Scorecard"

You say you want to look at the lower case name of the worksheet. But you
compare it to "Scorecard" with an uppercase S. It'll never match (well, unless
you have "option compare text" at the top of the module--but if you did, you
wouldn't need the LCase() stuff at all).

Phil Hageman wrote:

This code does not execute when the user clicks on the Print button. The code objective is stated below. One comment made is that in the select line, lower case is specified, where proper case is used in the case line. I changed the text in the case lines to lower case but the code still doesn't work. On thing: this workbook is a template used by many users. The worksheet tabs are typed in just as they appear in the code - Proper case. Does anyone have an idea to fix this code?

The object of this code is to reset the scaling and print area in Page Setup €“ before printing begins. It is located in Module2 of the workbook. To test the code, I set the scale at 50% for the worksheets and clicked on the print button. It prints at 50% instead of 95%/90%, as proposed in the code. Its as though Excel doesnt see the code at all. Can someone look through this as suggest a fix? I can compress the file and send it if necessary.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wsSheet As Worksheet
Dim rng As Range, ar As Range
Dim lngZ As Long
For Each wsSheet In ActiveWindow.SelectedSheets
Select Case LCase(wsSheet.Name)
Case "Scorecard"
lngZ = 95
With wsSheet
Set rng = .Range("B1:BA45")
End With
Case "Customer", "Financial", "Learning and Growth", "Internal Business Process"
lngZ = 90
With wsSheet
Set rng = .Range("B1:BA32,B33:BA64,B65:BA96")
End With
Exit Sub
Case Else
With wsSheet.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
Exit Sub
End Select
With wsSheet.PageSetup
.Zoom = lngZ
End With
Cancel = True
On Error GoTo ErrHandler
Application.EnableEvents = False
For Each ar In rng
ar.PrintOut
Next
Next
ErrHandler:
Application.EnableEvents = True
End Sub

--

Dave Peterson


--

Dave Peterson

Dave, Thanks for your answer. I move the code to ThisWorkbook (deleted from Module2) - still not working. I also looked at Chip's and your web pages but for me (not a programmer) it was like drinking from a fire hose! Could I prevail upon you to modify the code to accomodate proper case. I'm sure you are correct - the use of LCase will never hook up with the worksheet names. But I'm locked into the worksheet names

Thanks
Phil

Phil,

Same as I told you last time. Correct the case, put it in THisWorkbok, and
who knows, it might work


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wsSheet As Worksheet
Dim rng As Range, ar As Range
Dim lngZ As Long
For Each wsSheet In ActiveWindow.SelectedSheets
Select Case LCase(wsSheet.Name)
Case "scorecard"
lngZ = 95
With wsSheet
Set rng = .Range("B1:BA45")
End With
Case "Customer", "fnancial", "learning and growth", "internal
business process"
lngZ = 90
With wsSheet
Set rng = .Range("B1:BA32,B33:BA64,B65:BA96")
End With
Exit Sub
Case Else
With wsSheet.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
Exit Sub
End Select
With wsSheet.PageSetup
.Zoom = lngZ
End With
Cancel = True
On Error GoTo ErrHandler
Application.EnableEvents = False
For Each ar In rng
ar.PrintOut
Next
Next
ErrHandler:
Application.EnableEvents = True
End Sub



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Phil Hageman" wrote in message
...
Dave, Thanks for your answer. I move the code to ThisWorkbook (deleted
from Module2) - still not working. I also looked at Chip's and your web
pages but for me (not a programmer) it was like drinking from a fire hose!
Could I prevail upon you to modify the code to accomodate proper case. I'm
sure you are correct - the use of LCase will never hook up with the
worksheet names. But I'm locked into the worksheet names.

Thanks,
Phil

What does LCase("Hello") return? Or LCase("hElLo")?

Will that match the value of a statement Case "Hello"?


--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Dave, Thanks for your answer. I move the code to ThisWorkbook (deleted from Module2) - still not working. I also looked at Chip's and your web pages but for me (not a programmer) it was like drinking from a fire hose! Could I prevail upon you to modify the code to accomodate proper case. I'm sure you are correct - the use of LCase will never hook up with the worksheet names. But I'm locked into the
worksheet names.

Thanks,
Phil

watchout for Bob's line:
Case "Customer", "fnancial", "learning and growth", "internal business process"

He got most of them <bg, but induced a typo:
Case "customer", "financial", "learning and growth", "internal business process"

Bob Phillips wrote:

Phil,

Same as I told you last time. Correct the case, put it in THisWorkbok, and
who knows, it might work

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wsSheet As Worksheet
Dim rng As Range, ar As Range
Dim lngZ As Long
For Each wsSheet In ActiveWindow.SelectedSheets
Select Case LCase(wsSheet.Name)
Case "scorecard"
lngZ = 95
With wsSheet
Set rng = .Range("B1:BA45")
End With
Case "Customer", "fnancial", "learning and growth", "internal
business process"
lngZ = 90
With wsSheet
Set rng = .Range("B1:BA32,B33:BA64,B65:BA96")
End With
Exit Sub
Case Else
With wsSheet.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
Exit Sub
End Select
With wsSheet.PageSetup
.Zoom = lngZ
End With
Cancel = True
On Error GoTo ErrHandler
Application.EnableEvents = False
For Each ar In rng
ar.PrintOut
Next
Next
ErrHandler:
Application.EnableEvents = True
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Phil Hageman" wrote in message
...
Dave, Thanks for your answer. I move the code to ThisWorkbook (deleted
from Module2) - still not working. I also looked at Chip's and your web
pages but for me (not a programmer) it was like drinking from a fire hose!
Could I prevail upon you to modify the code to accomodate proper case. I'm
sure you are correct - the use of LCase will never hook up with the
worksheet names. But I'm locked into the worksheet names.

Thanks,
Phil

--

Dave Peterson

Changed the typed text in the Case lines to lower case and selected Print Preview (remember, the worksheets are set up at 50% for the test - would expect the document to be full screen for a printout full page)
1. Screen is blank (white) with what looks like an upper right border corner line in the middle of the screen ( a black right angle line)
2. Unable to exit Print Preview. Clicking on Close, the screen flashes to the Normal view for about 1/4 of a second, and then back to Print Preview. Basically, Normal view is locked out
3. Visual Basic editor locked - clicking anywhere on screen produces an error tone - nothing else. "Close" option is off
4. Only way to close the workbook and VB editor is control-alt-delete

I didn't save it before testing, so I can open the workbook with the code in its original form

Now what? Should a completely different approach be taken here?

Thanks
Phi

Tushar

Thanks for your reply; however, I don't understand your question..

Phil

He's basically saying, why are you testing a value that you have converted
to lower-case against a proper case value and expecting it to match.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Phil Hageman" wrote in message
...
Tushar,

Thanks for your reply; however, I don't understand your question...

Phil

Don't step through your code when you're testing. And save before you test!



Phil Hageman wrote:

Changed the typed text in the Case lines to lower case and selected Print Preview (remember, the worksheets are set up at 50% for the test - would expect the document to be full screen for a printout full page):
1. Screen is blank (white) with what looks like an upper right border corner line in the middle of the screen ( a black right angle line).
2. Unable to exit Print Preview. Clicking on Close, the screen flashes to the Normal view for about 1/4 of a second, and then back to Print Preview. Basically, Normal view is locked out.
3. Visual Basic editor locked - clicking anywhere on screen produces an error tone - nothing else. "Close" option is off.
4. Only way to close the workbook and VB editor is control-alt-delete.

I didn't save it before testing, so I can open the workbook with the code in its original form.

Now what? Should a completely different approach be taken here?

Thanks,
Phil

--

Dave Peterson

Dave

Don't understand what you mean by stepping through the code when testing. My concept of testing is simply that with the code in place, I trigger the event as a user - in this case by: 1. clicking the print icon, 2. clicking print preview icon, or 3. clicking fileprintetc. I'm at a loss at this point - how do I get things working

To digress: one given is that the worksheet tab names have upper case in them (as proposed in the Case statements) - this has to remain a given. Fout questions; 1. Is the code correct? 2. If not, how should it be corrected? 3. Is what I'm proposing possible? 4. Is there a problem elsewhere in the way I have things set up in the workbook (like the code being in ThisWorkbook module)

Fortunately, I did save before testing - learned that lesson the hard way long ago, but still sage advice

Again, Thanks Dave for taking your time with this problem

Phil

Bob and Tushar

I don't expect it to match, I understand that point; but my point in all this: With it given that worksheet names have upper case in tab names, what should the code be to achieve full-page printing

Phil

Phil,

I have to admit that I have to sign-off this discussion now. When I tried
your code originally, way back in time now, I put it in ThisWorkbook, and I
corrected the case issue, and although it changed the zoom percentage as you
required, it then went into a loop that I couldn't recover from.

So I think there is another problem there, but I don't have the time or
energy to investigate it when it does that to my machine.

Regards

Bob

"Phil Hageman" wrote in message
...
Bob and Tushar,

I don't expect it to match, I understand that point; but my point in all
this: With it given that worksheet names have upper case in tab names, what
should the code be to achieve full-page printing?

Phil

You can add a breakpoint near the top of the code, then click the
Preview button
(to add a breakpoint, click in the grey bar at the left side of the
code window -- it will add a large red circle.)

This will activate the VBE, and the line that's highlighted in yellow is
about to run.
Press the F8 key to run that line, and move to the next line

This may help you see where things are going wrong.
For example, I'm not sure that you really want to exit the code in all
the places that you have the Exit Sub line.


Phil Hageman wrote:
Dave,

Don't understand what you mean by stepping through the code when testing. My concept of testing is simply that with the code in place, I trigger the event as a user - in this case by: 1. clicking the print icon, 2. clicking print preview icon, or 3. clicking fileprintetc. I'm at a loss at this point - how do I get things working?

To digress: one given is that the worksheet tab names have upper case in them (as proposed in the Case statements) - this has to remain a given. Fout questions; 1. Is the code correct? 2. If not, how should it be corrected? 3. Is what I'm proposing possible? 4. Is there a problem elsewhere in the way I have things set up in the workbook (like the code being in ThisWorkbook module)?

Fortunately, I did save before testing - learned that lesson the hard way long ago, but still sage advice.

Again, Thanks Dave for taking your time with this problem.

Phil


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Bob

Understand. Thank you very much for the time you did spend - I appreciate it

See you again sometime on another thread

Phil

When you use lcase(), it converts the string to lower case. You're not actually
changing the name of any workbook--you're just doing a comparison between two
strings.

Alternatively, you can drop all the lcase() stuff and just add:
Option Compare Text
at the top of the module.

Then you won't have to worry about Customer, customer, CuStOmEr, or any other
variation.

====
But I did have trouble running your code. When I was stepping through it, it
would lock up excel and I'd have to kill it to continue.

But it's probably not your code. John Walkenbach documented it on his web site:

http://j-walk.com/ss/excel/odd/odd11.htm

So either don't step through your code or be very careful (saving before you
step through it).



Phil Hageman wrote:

Dave,

Don't understand what you mean by stepping through the code when testing. My concept of testing is simply that with the code in place, I trigger the event as a user - in this case by: 1. clicking the print icon, 2. clicking print preview icon, or 3. clicking fileprintetc. I'm at a loss at this point - how do I get things working?

To digress: one given is that the worksheet tab names have upper case in them (as proposed in the Case statements) - this has to remain a given. Fout questions; 1. Is the code correct? 2. If not, how should it be corrected? 3. Is what I'm proposing possible? 4. Is there a problem elsewhere in the way I have things set up in the workbook (like the code being in ThisWorkbook module)?

Fortunately, I did save before testing - learned that lesson the hard way long ago, but still sage advice.

Again, Thanks Dave for taking your time with this problem.

Phil

--

Dave Peterson

Here is a revision. The endless loop was an omission of Areas in

For Each ar In rng

which should be:

For Each ar In rng.Areas

So it isn't an endless loop, but doing a cell at a time. (without the
correction).


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wsSheet As Worksheet
Dim rng As Range, ar As Range
Dim lngZ As Long
MsgBox "In BeforePrint"
For Each wsSheet In ActiveWindow.SelectedSheets
Select Case LCase(wsSheet.Name)
Case "scorecard"
lngZ = 95
With wsSheet
Set rng = .Range("B1:BA45")
End With
Case "customer", "financial", "learning", "process"
lngZ = 90
With wsSheet
Set rng = .Range("B1:BA32,B33:BA64,B65:BA96")
End With
Case Else
With wsSheet.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
Exit Sub
End Select
vVal = wsSheet.PageSetup.Zoom
With wsSheet.PageSetup
.Zoom = lngZ
End With
Cancel = True
On Error GoTo ErrHandler
Application.EnableEvents = False
For Each ar In rng.Areas
MsgBox "Zoom: " & wsSheet.PageSetup.Zoom _
& " - " & ar.Address(external:=True)
ar.PrintOut
Next
Next
ErrHandler:
Application.EnableEvents = True
End Sub

Seemed to work for me - but it certainly hasn't been exhaustively tested.

but for me (not a programmer)

If you want to use code to accomplish your task, then you best learn how to
program or pay someone that can. This forum really isn't a resource to get
free code. When you ask for help, make sure you state at the top - looking
for free coding service, I am not a programmer.

To test code like this, someone has to recreate key elements of your
workbook - which takes a lot of time and effort. If I have that time, I
will test my code, but in this case, I did not. I suppose I should have
stated that explicitly.

When you ask how can I do something, the answers are not meant to be fully
tested turnkey solutions. Thus you have an implicit obligation to invest
your own time in implementing the suggestions you want to use in developing
your own solution.


--
Regards,
Tom Ogilvy

"Phil Hageman" wrote in message
...
Bob,

Understand. Thank you very much for the time you did spend - I appreciate
it!

See you again sometime on another thread.

Phil

Okay - sounds like a positive direction. In ThisWorkbook there is only one Sub. Preceding the Sub is Option Explicit. Can you advise me exactly where to put Option Compare Text? Is it in the Sub

Also, do I simply delete the line containing "LCase"

In Debra's answer, she raises the advisability of using Exit Sub. I commented these lines out but couldn't determine anything since the code in general isn't working. What is your opinion

Phil

You can declare it, or you can remove it

remove the line
vVal = wsSheet.PageSetup.Zoom





--
Regards,
Tom Ogilvy

"Phil Hageman" wrote in message
...
Tom,

I copied this latest into the ThisWorkbook module (Option Explicit is set)
and received a compile error: Variable not defined. Highlighted is: vVal =

Thanks, and I appreciate what you are trying to do for me.

Phil

I think Tom's code is what you want. So don't do those changes.

(when I read your original code, I thought you wanted to print out each
cell--something weird, but I figured that you knew what you wanted. Tom
realized that that would be nuts (technical term) and modified your code to
print out each Area in your range.)

But just as closu

"Option Compare Text" can go directly below "Option Explicit"

you'd keep the line, but remove the word lcase (and maybe the extra
parentheses).



Phil Hageman wrote:

Okay - sounds like a positive direction. In ThisWorkbook there is only one Sub. Preceding the Sub is Option Explicit. Can you advise me exactly where to put Option Compare Text? Is it in the Sub?

Also, do I simply delete the line containing "LCase"?

In Debra's answer, she raises the advisability of using Exit Sub. I commented these lines out but couldn't determine anything since the code in general isn't working. What is your opinion?

Phil

--

Dave Peterson

Dave, Thanks very much for your advice through all this - I appreciate it.

Tom, I removed the line and the code works! Thank you very much

When the message box comes up asking for OK to print a particular range, can we add a "no" option to not print that range? Sometimes the second or third range may be empty of data

Thanks
Phil

If you just want to stop the printing of ranges with nothing in them, this could
be at the bottom of your code:

For Each ar In rng.Areas
MsgBox "Zoom: " & wsSheet.PageSetup.Zoom _
& " - " & ar.Address(external:=True)
If Application.CountA(ar) 0 Then
ar.PrintOut
End If
Next

application.counta() just counts the cells that have something in them.

But it will include formulas that evaluate to "".

Phil Hageman wrote:

Tom, I removed the line and the code works! Thank you very much.

When the message box comes up asking for OK to print a particular range, can we add a "no" option to not print that range? Sometimes the second or third range may be empty of data.

Thanks,
Phil

--

Dave Peterson

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wsSheet As Worksheet
Dim rng As Range, ar As Range
Dim lngZ As Long
Dim ans as Variant
' MsgBox "In BeforePrint"
For Each wsSheet In ActiveWindow.SelectedSheets
Select Case LCase(wsSheet.Name)
Case "scorecard"
lngZ = 95
With wsSheet
Set rng = .Range("B1:BA45")
End With
Case "customer", "financial", "learning", "process"
lngZ = 90
With wsSheet
Set rng = .Range("B1:BA32,B33:BA64,B65:BA96")
End With
Case Else
With wsSheet.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
Exit Sub
End Select
vVal = wsSheet.PageSetup.Zoom
With wsSheet.PageSetup
.Zoom = lngZ
End With
Cancel = True
On Error GoTo ErrHandler
Application.EnableEvents = False
For Each ar In rng.Areas
ans = _
MsgBox( "Zoom: " & wsSheet.PageSetup.Zoom _
& " - " & ar.Address(external:=True) & _
vbNewLine & vbNewline & "Print this out?", vbYesNo)
if ans = vbYes then
ar.PrintOut
end if
Next
Next
ErrHandler:
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy


"Phil Hageman" wrote in message
...
Tom, I removed the line and the code works! Thank you very much.

When the message box comes up asking for OK to print a particular range,
can we add a "no" option to not print that range? Sometimes the second or
third range may be empty of data.

Thanks,
Phil

Tom

This works great. Thanks. A final, I think, refinement. In the message box, can we substitute the file name with Page numbers and number of copies

For example, the message box would say: Print Page 1? Yes No (We can omit reference to the zoom size)
according to the following schedule

On "scorecard", Page 1 (the only page) would be for range B1:BA4

On "financial", "learning", and "process", Page 1 would be for range B1:BA32; Page 2 would be for B33:BA64
and Page 3 would be for B65:BA96. Would there be a way to ask for all three print pages in one message box, with th
number of copies

Example: Print Page 1 ? Yes No Number of Copies: __
Print Page 2 ? Yes No Number of Copies: __
Print Page 3 ? Yes No Number of Copies: __
Thanks
Phil

You would need to put up a userform to solicit multiple values from the
user. A message box only offers buttons to click. an Input box allows entry
of a single answer.

--
Regards,
Tom Ogilvy

"Phil Hageman" wrote in message
...
Tom,

This works great. Thanks. A final, I think, refinement. In the message
box, can we substitute the file name with Page numbers and number of copies?

For example, the message box would say: Print Page 1? Yes No (We
can omit reference to the zoom size),
according to the following schedule:

On "scorecard", Page 1 (the only page) would be for range B1:BA45

On "financial", "learning", and "process", Page 1 would be for range
B1:BA32; Page 2 would be for B33:BA64;
and Page 3 would be for B65:BA96. Would there be a way to ask for all
three print pages in one message box, with the
number of copies?

Example: Print Page 1 ? Yes No Number of Copies: ___
Print Page 2 ? Yes No Number of Copies: ___
Print Page 3 ? Yes No Number of Copies: ___
Thanks,
Phil