Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel hanging up?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel hanging up?
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" 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel hanging up?
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 "Jim Cone" wrote in message ... 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" 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel hanging up?
As a matter of curiosity, how do you define ColCount and ColPerPage variables
in the print set up procedure? "Otto Moehrbach" wrote: 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 "Jim Cone" wrote in message ... 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" 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel hanging up?
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" 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 "Jim Cone" wrote in message ... 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" 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel hanging up?
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" wrote in message ... As a matter of curiosity, how do you define ColCount and ColPerPage variables in the print set up procedure? "Otto Moehrbach" wrote: 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 "Jim Cone" wrote in message ... 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" 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel hanging up?
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" wrote: 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" wrote in message ... As a matter of curiosity, how do you define ColCount and ColPerPage variables in the print set up procedure? "Otto Moehrbach" wrote: 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 "Jim Cone" wrote in message ... 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" 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel hanging up?
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 "JLGWhiz" wrote in message ... 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" wrote: 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" wrote in message ... As a matter of curiosity, how do you define ColCount and ColPerPage variables in the print set up procedure? "Otto Moehrbach" wrote: 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 "Jim Cone" wrote in message ... 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" 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel hanging up?
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 "JLGWhiz" wrote in message ... 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" wrote: 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" wrote in message ... As a matter of curiosity, how do you define ColCount and ColPerPage variables in the print set up procedure? "Otto Moehrbach" wrote: 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 "Jim Cone" wrote in message ... 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" 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel hanging up?
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 "Jim Cone" wrote in message ... 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" 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel hanging up?
Good show, Otto! Makes one feel good when those bugs are exterminated.
"Otto Moehrbach" wrote: 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 "Jim Cone" wrote in message ... 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" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel hanging... | Excel Discussion (Misc queries) | |||
Can you use a hanging indent in an Excel cell? | Excel Discussion (Misc queries) | |||
Can you use hanging indents in Excel? | Excel Discussion (Misc queries) | |||
Excel hanging up | Excel Programming | |||
Hanging Instances Of EXCEL.EXE? | Excel Programming |