Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Print Preview vertical scrolling & Next / Previous page are not working

0

I have a VB reporting application that works correctly in Excel 2010, but not in Excel 2016 or the versions between.  

The Activesheet.PrintPreview is called from a macro.  It is displayed correctly but scrolling and next page / previous page are not working.  

The report was being opened up in compatibality mode.  I thought that this might be causing the problem.  Since posting the first question, I have converted the file from .XLS to .XLSM format.  The report is no longer opening in compatibility mode.  However the problem is still there.  I also checked for hidden items and there aren't any.  

When print preview is being displayed.  I can click next page and it see that I clicked it because the previous page is enabled.  The page does not change until I click on the report.  The scrollbar behaves the same way.

Macro

Public Sub sbPreviewQLSReport(spdfFile As String, sODInfo As String)
'Procedure name is changed from sbPreviewReport so that all new qls releases
'that 2.10 and above wil call the new procedure
   gsPDFFileName = spdfFile

    gsOSInfo = sODInfo
    Call SbSetupWindowsDirectory

    'Keep app minimised while it is being configured.
    mlWindowState = Application.WindowState
    Application.WindowState = xlMaximized
    'Prevent screen changes to speed up report processing.
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    'Setup application globals.
    Call sbSetWorkSheetVariableNames

    ActiveSheet.PrintPreview
    ThisWorkbook.Windows(1).Visible = False
    'Show the user the preview.

    Application.ScreenUpdating = True
End Sub
excel
MS Office Version: 2016
posted by: Billiam (rep: 10) on Mon Jul 25, 2016 at 2:21 pm
Answer
Discuss

Discussion

Is it possible to provide a sample file? It's otherwise difficult to troubleshoot the issue.
don (rep: 1989) Jul 25, '16 at 4:26 pm
The reports data is added to excel from the application.  This is the macro that is called to display the report.  It passes in the a file name and the WIN Version being used.  
Public Sub sbPreviewQLSReport(spdfFile As String, sODInfo As String)
'Procedure name is changed from sbPreviewReport so that all new qls releases
'that 2.10 and above wil call the new procedure
   gsPDFFileName = spdfFile
    
    gsOSInfo = sODInfo
    Call SbSetupWindowsDirectory
        
    'Keep app minimised while it is being configured.
    mlWindowState = Application.WindowState
    Application.WindowState = xlMaximized
    'Prevent screen changes to speed up report processing.
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    'Setup application globals.
    Call sbSetWorkSheetVariableNames
    
    ActiveSheet.PrintPreview
    ThisWorkbook.Windows(1).Visible = False
    'Show the user the preview.
    
    Application.ScreenUpdating = True
End Sub


The report was being opened up in compatibality mode.  I thought that this might be causing the problem.  Since posting the first question, I have converted the file from .XLS to .XLSM format.  The report is no longer opening in compatibility mode.  However the problem is still there.  I also checked for hidden items and there aren't any.  
Billiam (rep: 10) Jul 26, '16 at 9:08 am
When print preview is being displayed.  I can click next page and it see that I clicked it because the previous page is enabled.  The page does not change until I click on the report.  The scrollbar behaves the same way.
Billiam (rep: 10) Jul 26, '16 at 9:40 am
Please update your original question with the new information! When people come here in the future they may not read the discussion section.
don (rep: 1989) Jul 26, '16 at 9:59 am
Add to Discussion

Answers

0
Selected Answer

I steped through the code and executed each line of the macro followed by executing the PrintPreview.  It was time consuming to do, but I identified the line of code that caused the problem and removed it.

Application.ScreenUpdating = False

After removing it everything worked fine.

posted by: Billiam (rep: 10) on Wed Jul 27, 2016 at 8:16 am
Discuss

Discussion

Glad that solved it! I thought that wouldn't be an issue since it was set to True at the end of the macro but then again I'm still in Excel 2010.

Make sure to select this as the correct answer when the system lets you, in a day I think.
don (rep: 1989) Jul 27, '16 at 10:56 am
Add to Discussion
0

Everything worked fine for me but you might try removing this line of code:

ThisWorkbook.Windows(1).Visible = False

Also, maybe remove this line or place it at the end of the macro but set to True

Application.DisplayAlerts = False

(and next time please update your original answer with the vba code - it's really hard to read in a comment)

posted by: don (rep: 1989) on Tue Jul 26, 2016 at 10:35 pm
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login