Re-Post: Before_Print Sub Doesn't Trigger

[Guest]

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

 

[Bob Phillips]

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)

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.

 

[Dave Peterson]

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).

 

[Dave Peterson]

And for your reading pleasure:

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

and

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

 

[Guest]

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

 

[Bob Phillips]

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)

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.

 

[Tushar Mehta]

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

 

[Dave Peterson]

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"

 

[Guest]

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

 

[Guest]

Tushar

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

Phil

 

[Bob Phillips]

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)

 

[Dave Peterson]

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

 

[Guest]

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 file>print>etc. 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

 

[Guest]

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

 

[Bob Phillips]

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

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?

 

[Debra Dalgleish]

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.

 

[Guest]

Bob

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

See you again sometime on another thread

Phil

 

[Dave Peterson]

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).

 

[Tom Ogilvy]

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.

 

[Guest]

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