Excel hanging up?

[Otto Moehrbach]

Excel XP, Win XP

Please read this entire message. It flows easily. I promise!

I'm helping an OP and I have a problem I can't figure out.

I have only one file with only one sheet.

I wrote 2 completely interdependent procedures.

Procedure #1 hides rows and columns as a function of a selection made by
clicking on one of several buttons in a UserForm. Works fine and takes
about 1 second.



Procedure #2 is a print procedure. A Workbook_BeforePrint macro cancels the
print command and calls Procedure #2. Procedure #2 sets the print range and
determines the number of columns to be printed (the visible columns) and
prints. This works fine also if ( a BIG IF) I leave it at that. (Code takes
about 0.5 seconds to run, then prints)

But I have additional code in Procedure #2 to set Portrait or Landscape as a
function of the number of columns to be printed. Let's call that additional
code Portrait/Landscape. And therein lays the problem. With that
Portrait/Landscape code in place, if I run Procedure #2, Procedure #2 takes
a very long time to run. But worse than that, after that, Procedure #1
takes a very, very, long time to run. Remember that the 2 procedures are
completely independent. It seems like Excel is getting into some state
(almost hung-up) if the Portrait/Landscape code runs. If I close the file
and open the file, everything is fine with Procedure #1 until I again run
Procedure #2. I have also cleaned the project with Rob Bovey's Code
Cleaner.



I have written the Portrait/Landscape code as:

If ColCount > ColPerPage Then

Call SetPortrait

Else

Call SetLandscape

End If

The SetPortrait macro I have written as:

With ActiveSheet.PageSetup

.Orientation = xlLandscape

.FitToPagesWide = 1

.FitToPagesTall = False

End With

The Set Landscape macro I have written as :

With ActiveSheet.PageSetup

..Orientation = xlPortrait

..Zoom = 100

End With

I have also written those 2 macros with everything you get when you record a
macro and set Portrait and Landscape manually. Same problem.

Question: What is happening to cause this and what should I do differently?

Thanks for taking the time to read this rather lengthy dissertation. Otto

 

[Jim Cone]

Otto,
Two things to try...
Set DisplayPageBreaks = False before calling PageSetUp
Set the .Zoom setting to 100 before changing any other PageSetUp properties.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Otto Moehrbach" <[email protected]>
wrote in message
Excel XP, Win XP
Please read this entire message. It flows easily. I promise!
I'm helping an OP and I have a problem I can't figure out.
I have only one file with only one sheet.
I wrote 2 completely interdependent procedures.
Procedure #1 hides rows and columns as a function of a selection made by
clicking on one of several buttons in a UserForm. Works fine and takes
about 1 second.
Procedure #2 is a print procedure. A Workbook_BeforePrint macro cancels the
print command and calls Procedure #2. Procedure #2 sets the print range and
determines the number of columns to be printed (the visible columns) and
prints. This works fine also if ( a BIG IF) I leave it at that. (Code takes
about 0.5 seconds to run, then prints)

But I have additional code in Procedure #2 to set Portrait or Landscape as a
function of the number of columns to be printed. Let's call that additional
code Portrait/Landscape. And therein lays the problem. With that
Portrait/Landscape code in place, if I run Procedure #2, Procedure #2 takes
a very long time to run. But worse than that, after that, Procedure #1
takes a very, very, long time to run. Remember that the 2 procedures are
completely independent. It seems like Excel is getting into some state
(almost hung-up) if the Portrait/Landscape code runs. If I close the file
and open the file, everything is fine with Procedure #1 until I again run
Procedure #2. I have also cleaned the project with Rob Bovey's Code
Cleaner.

I have written the Portrait/Landscape code as:

If ColCount > ColPerPage Then
Call SetPortrait
Else
Call SetLandscape
End If

The SetPortrait macro I have written as:

With ActiveSheet.PageSetup
.Orientation = xlLandscape
.FitToPagesWide = 1
.FitToPagesTall = False
End With

The Set Landscape macro I have written as :

With ActiveSheet.PageSetup
..Orientation = xlPortrait
..Zoom = 100
End With

I have also written those 2 macros with everything you get when you record a
macro and set Portrait and Landscape manually. Same problem.
Question: What is happening to cause this and what should I do differently?
Thanks for taking the time to read this rather lengthy dissertation. Otto

 

[Otto Moehrbach]

Jim
Thanks for your help. But it didn't solve this problem. Here is the
code as I have it now with your suggestions incorporated. This code is in
the Print macro. I even tried it with the first ".Zoom = 100" remarked out.
Note that with this code (all the code below) remarked out, the Print macro
runs fine, except that it's printing in Portrait only. Also, with this code
remarked out, the effect on the first procedure is zero, which is good.
Bottom line, though, is that I can't setup to print in Landscape. Your help
is very much appreciated. Otto
ActiveSheet.DisplayPageBreaks = False
With ActiveSheet.PageSetup
If ColCount > ColPerPage Then
.Zoom = 100
.Orientation = xlLandscape
.FitToPagesWide = 1
.FitToPagesTall = False
Else
.Zoom = 100
.Orientation = xlPortrait
End If
End With

 

[JLGWhiz]

As a matter of curiosity, how do you define ColCount and ColPerPage variables
in the print set up procedure?

 

[Jim Cone]

Otto,
I believe JLGW may be on to something.
I have no further suggestions.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Otto Moehrbach" <[email protected]>
wrote in message
Jim
Thanks for your help. But it didn't solve this problem. Here is the
code as I have it now with your suggestions incorporated. This code is in
the Print macro. I even tried it with the first ".Zoom = 100" remarked out.
Note that with this code (all the code below) remarked out, the Print macro
runs fine, except that it's printing in Portrait only. Also, with this code
remarked out, the effect on the first procedure is zero, which is good.
Bottom line, though, is that I can't setup to print in Landscape. Your help
is very much appreciated. Otto
ActiveSheet.DisplayPageBreaks = False
With ActiveSheet.PageSetup
If ColCount > ColPerPage Then
.Zoom = 100
.Orientation = xlLandscape
.FitToPagesWide = 1
.FitToPagesTall = False
Else
.Zoom = 100
.Orientation = xlPortrait
End If
End With

 

[Otto Moehrbach]

Thanks for helping.
The file has a number of non-contiguous hidden columns. ColCount is
declared as long and is equal to the number of visible cells in Row 4.
ColCount = Range("A4", Cells(4, Columns.Count).End(xlToLeft)) _
.SpecialCells(xlCellTypeVisible).Count
Looking at the file, ColCount has always been correct.

ColPerPage is simply the number of columns (all the same width) at which
point the decision to go to Landscape occurs. The value is determined by
the OP's printer and printer driver and is a constant. It is declared as:
Const ColPerPage As Long = 10
Otto

 

[JLGWhiz]

Otto, the reason I asked about the variables was to see if there was some
kind of array that you were using that might eat up a bunch of memory. That
can sometimes cause the system to slow down, but I don't see anything in what
you explained. The other thing I look for when I encounter these type of
problems is a loop that might run longer than I mean for it to. Sometimes I
get careless with my parameters and don't catch the glitches until I run the
full code. Then I cuss a lot at my stupidity. Sorry I can't help more.

 

[Otto Moehrbach]

Thanks for your time. Let me bounce an idea off of you.
Because this file is the OP's file, and because she had things like
Outline/Grouping (I took it out), and because she developed the file through
the old process of fits and starts and 3 steps forward and 2 steps back, and
so forth and so forth over some period of time, I'm thinking the file might
be corrupt. I have seen this before.
So I am now in the process of rebuilding her file from scratch starting
with a new blank file and not copying sheets. I am copying the code by
running it through Word first, and so on. Does it sound plausible that the
file could be corrupt? Otto

 

[Otto Moehrbach]

I hope you're still with me. I just found something very significant. You
remember that I said that the first procedure operates very, very, slow if I
once run procedure #2, the print procedure? Now I found that I don't have
to run the print procedure at all to cause the slow operation. All I have
to do is manually click on File - Page Setup - OK. That triggers the
problem. The OP has custom footers, and rows to print on every page. Any
ideas? Otto

 

[Otto Moehrbach]

Jim & JLGWhiz
Thanks to both of you for your help. From what both of you said, I
cranked the brain and went through many code scenarios to nail down the one
thing that was causing my problem. I think I came up with it.
It appears that there is an incompatibility between having pagebreaks
displayed and running any code that hides/unhides rows/columns. Jim, I took
your line:
ActiveSheet.DisplayPageBreaks = False
and placed it before the hide/unhide code as well as before the code in the
print macro that accessed PageSetup and the problem was solved.
Thanks again, both of you. Otto

 

[JLGWhiz]

Good show, Otto! Makes one feel good when those bugs are exterminated.