Excel 2013 Print Area breaks Excel

D

davidltp

New Member
Joined
Nov 26, 2014
Messages
1
I am looking at someone's code and I can't figure out why this particular issue is happening. There is a Sample.xlsm document with four buttons that run four similar functions with slight variations. One of the function breaks Excel causing the complete top menu bar to disappear. It does not disable it, it literally disappears, it is translucent space.
I have used breakpoints throughout the code (on all four functions) and it seems when the code is run slowly it works fine, but when it runs at regular speed it break Excel.

Please provide some assistance.

Code that is not working:
Code: 
Sub SC2DIFFRUTHDOWNLOAD()

Dim fdsapi As New FactsetApi
Dim Result
    
Result = fdsapi.RunApplication("UNIVERSAL SCREENING", "BATCH MODE = TRUE", _
   "SCREEN=CLIENT:SCREENS/FIXED_INCOME/SC2DIFF_RUTH", "OUTPUT TYPE=DOWNLOAD", _
  "#Y = 0", "#Q = -1", "OUTPUT FILENAME=H:\Factset\SC2DIFF_RUTH.XLS", _
    "EXIT AFTER OUTPUT = TRUE")
    
   Workbooks.Open Filename:="H:\Factset\SC2DIFF_RUTH.XLS"
   Windows("SC2DIFF_RUTH.XLS").Activate
   Application.ScreenUpdating = False
    ActiveWindow.FreezePanes = False
    ActiveWindow.SplitColumn = 5
    ActiveWindow.FreezePanes = True
     ActiveWindow.Zoom = 90
    
Range("E7", Range("E7").End(xlToRight).Offset(500, 0)).Select


    Selection.NumberFormat = "0.00"
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
    Range("C2").Value = "15"
    Range("A2").Value = "BASIS POINTS-->"
    Rows("2:2").RowHeight = 24.75
    Range("A2:B2").Font.Bold = True
    Range("C2").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
     Range("C2").Select
    With Selection.Font
        .Name = "Arial"
        .Size = 14
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
    End With
    Selection.Font.Bold = True
    With Selection.Interior
        .ColorIndex = 44
        .Pattern = xlSolid
    End With
    Range("A2:B2").Select
    With Selection.Interior
        .ColorIndex = 44
        .Pattern = xlSolid
    End With
    Range("A2:C2").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    
Range("E7", Range("E7").End(xlToRight).Offset(500, 0)).Select


Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotBetween, _
        Formula1:="=""(-.01*$C$2)+$E7""", Formula2:="=""(.01*$C$2)+$E7"""
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
    End With
    Selection.FormatConditions(1).Interior.ColorIndex = 46
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotBetween, _
        Formula1:="=(-0.01*$C$2)+$E7", Formula2:="=(0.01*$C$2)+$E7"
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
    End With


    Selection.FormatConditions(1).Interior.ColorIndex = 46
   


   
 Application.DisplayAlerts = False
 Application.ScreenUpdating = False
 


    Range("f2:j2").Select
    ActiveCell.FormulaR1C1 = "Small Cap II in Alpha Order"
    Range("f2:j2").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
   
    Selection.Font.ColorIndex = 3
    With Selection.Font
        .Name = "Arial"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 3
    End With
    Selection.Font.Bold = True
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone




Range("E1").FormulaR1C1 = "=TODAY()"
    Range("E1").Select
    Selection.NumberFormat = "d-mmm-yy"
    
    
    Range("E1").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Font.Bold = True
    


    
'###Insert Rows For Pyle
Range("B300").Value = "End"
'Range("B6").EntireRow.Insert
Range("B7").Select
Do Until Selection.Value = "End"
Selection.End(xlDown).Select
Selection.EntireRow.Insert
Selection.Offset(1, 0).Select
Loop
'##### Format Printing
ActiveWorkbook.Names.Add Name:="printer", RefersToR1C1:=Range("A1", Range("j500").End(xlUp))




    
    ActiveSheet.PageSetup.PrintArea = "printer"


    
    
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$2:$5"
        .PrintTitleColumns = "$A:$E"
    End With
       
 
    With ActiveSheet.PageSetup
        .LeftHeader = "&"",Regular""&9"
        .CenterHeader = "&"",Regular""&9"
        .RightHeader = "&"",Regular""&9"
        .LeftFooter = "&"",Regular""&9"
        .CenterFooter = "&"",Regular""&9"
        .RightFooter = "&"",Regular""&9"
        .LeftMargin = Application.InchesToPoints(0.25)
        .RightMargin = Application.InchesToPoints(0.25)
        .TopMargin = Application.InchesToPoints(0.25)
        .BottomMargin = Application.InchesToPoints(0.25)
        .HeaderMargin = Application.InchesToPoints(0.25)
        .FooterMargin = Application.InchesToPoints(0.25)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = True
        .CenterVertically = True
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 3
        .FitToPagesTall = 1
        .PrintErrors = xlPrintErrorsDisplayed
    End With


 
Application.DisplayAlerts = False
        Range("A1").Select
        ActiveWorkbook.SaveAs Filename:="H:\Factset\SC2DIFF_RUTH.XLS", FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
    
Application.ScreenUpdating = True
Application.DisplayAlerts = True
 
 Range("f7").Select
End Sub

Code that is working:
Code: 
Sub SCDIFFRUTHDOWNLOAD()

Dim fdsapi As New FactsetApi
Dim Result
    
Result = fdsapi.RunApplication("UNIVERSAL SCREENING", "BATCH MODE = TRUE", _
   "SCREEN=CLIENT:SCREENS/FIXED_INCOME/SCDIFF_RUTH", "OUTPUT TYPE=DOWNLOAD", _
  "#Y = 0", "#Q = -1", "OUTPUT FILENAME=H:\Factset\SCDIFF_RUTH.XLS", _
    "EXIT AFTER OUTPUT = TRUE")
    
   Workbooks.Open Filename:="H:\Factset\SCDIFF_RUTH.XLS"
   Windows("SCDIFF_RUTH.XLS").Activate
   Application.ScreenUpdating = False
 ActiveWindow.FreezePanes = False
    ActiveWindow.SplitColumn = 5
    ActiveWindow.FreezePanes = True
     ActiveWindow.Zoom = 90
    
Range("E7", Range("E7").End(xlToRight).Offset(500, 0)).Select


    Selection.NumberFormat = "0.00"
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
    Range("C2").Value = "15"
    Range("A2").Value = "BASIS POINTS-->"
    Rows("2:2").RowHeight = 24.75
    Range("A2:B2").Font.Bold = True
    Range("C2").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
     Range("C2").Select
    With Selection.Font
        .Name = "Arial"
        .Size = 14
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
    End With
    Selection.Font.Bold = True
    With Selection.Interior
        .ColorIndex = 44
        .Pattern = xlSolid
    End With
    Range("A2:B2").Select
    With Selection.Interior
        .ColorIndex = 44
        .Pattern = xlSolid
    End With
    Range("A2:C2").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    
Range("E7", Range("E7").End(xlToRight).Offset(500, 0)).Select


Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotBetween, _
        Formula1:="=""(-.01*$C$2)+$E7""", Formula2:="=""(.01*$C$2)+$E7"""
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
    End With
    Selection.FormatConditions(1).Interior.ColorIndex = 46
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotBetween, _
        Formula1:="=(-0.01*$C$2)+$E7", Formula2:="=(0.01*$C$2)+$E7"
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
    End With


    Selection.FormatConditions(1).Interior.ColorIndex = 46
   


   
 Application.DisplayAlerts = False
 Application.ScreenUpdating = False
 


    Range("E2:z2").Select
    ActiveCell.FormulaR1C1 = "Small Cap Accounts in Alpha Order"
    Range("E2:z2").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
   
    Selection.Font.ColorIndex = 3
    With Selection.Font
        .Name = "Arial"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 3
    End With
    Selection.Font.Bold = True
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone




Range("E1").FormulaR1C1 = "=TODAY()"
    Range("E1").Select
    Selection.NumberFormat = "d-mmm-yy"
    
    
    Range("E1").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Font.Bold = True
    


    
'###Insert Rows For Pyle
Range("B300").Value = "End"
'Range("B6").EntireRow.Insert
Range("B7").Select
Do Until Selection.Value = "End"
Selection.End(xlDown).Select
Selection.EntireRow.Insert
Selection.Offset(1, 0).Select
Loop
'##### Format Printing
ActiveWorkbook.Names.Add Name:="printer", RefersToR1C1:=Range("A1", Range("z500").End(xlUp))




    
    ActiveSheet.PageSetup.PrintArea = "printer"


    
    
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$2:$5"
        .PrintTitleColumns = "$A:$E"
    End With
       
 
    With ActiveSheet.PageSetup
        .LeftHeader = "&"",Regular""&9"
        .CenterHeader = "&"",Regular""&9"
        .RightHeader = "&"",Regular""&9"
        .LeftFooter = "&"",Regular""&9"
        .CenterFooter = "&"",Regular""&9"
        .RightFooter = "&"",Regular""&9"
        .LeftMargin = Application.InchesToPoints(0.25)
        .RightMargin = Application.InchesToPoints(0.25)
        .TopMargin = Application.InchesToPoints(0.25)
        .BottomMargin = Application.InchesToPoints(0.25)
        .HeaderMargin = Application.InchesToPoints(0.25)
        .FooterMargin = Application.InchesToPoints(0.25)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = True
        .CenterVertically = True
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 3
        .FitToPagesTall = 1
        .PrintErrors = xlPrintErrorsDisplayed
    End With


 
Application.DisplayAlerts = False
        Range("A1").Select
        ActiveWorkbook.SaveAs Filename:="H:\Factset\SCDIFF_RUTH.XLS", FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
    
Application.ScreenUpdating = True
Application.DisplayAlerts = True
 
 Range("f7").Select
End Sub
 
You must log in or register to reply here.

Similar threads

K
  • Solved
formating question
Replies
3
Views
357
Micron
M
L
  • Locked
  • Question Question
Help Cleaning Up Code
Replies
2
Views
682
RoryA
RoryA
K
  • Solved
create a button that do actions
2 3
Replies
22
Views
1K
kerm007
K
P
  • Question Question
Issue with button not running in spread sheet only in test mode
Replies
1
Views
397
6StringJazzer
6StringJazzer
S
  • Question Question
Named Ranges Just Stopped Working Correctly
Replies
3
Views
234
SewStage
S

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top