when running the macro screen goes blank
when running the macro screen goes blank
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: when running the macro screen goes blank

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I am running the excel macro and screen goes blank and hour glass is still there how to get rid of the screen going blank if at all it is possible.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Chippenham, UK
    Posts
    136
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-13 12:29, ap1 wrote:
    I am running the excel macro and screen goes blank and hour glass is still there how to get rid of the screen going blank if at all it is possible.
    Can you put up the code?

    Does it have:

    application.screenUpdating = false

    in the macro?

    Regards,

    Gary Hewitt-Long

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ChDir "C:Data"
    Workbooks.OpenText Filename:="C:DataEpdbBillAddress", Origin:= _
    xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
    xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
    Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo:= _
    Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7 _
    , 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1))
    Selection.EntireRow.Insert
    ActiveCell.FormulaR1C1 = "EPDB PIN"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = ""
    Range("A1").Select
    Selection.ClearContents
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "EPDB PIN"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "EPDB Billing Unit #"
    Range("F1").Select
    Columns("E:E").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
    Columns("D:D").Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .ShrinkToFit = False
    .MergeCells = False
    End With
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "EPDB Provider Name"
    Range("F2").Select
    Columns("F:F").EntireColumn.AutoFit
    ActiveWindow.SmallScroll ToRight:=4
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "EPDB Billing Addr #"
    Range("G2").Select
    Columns("G:G").EntireColumn.AutoFit
    Columns("G:G").Select
    With Selection
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .MergeCells = False
    End With
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "EPDB Billing Addr Bldg NM"
    Range("H2").Select
    Columns("H:H").EntireColumn.AutoFit
    ActiveWindow.SmallScroll ToRight:=2
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "EPDB Billing Addr Line 1"
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "EPDB Billing Addr Line 2"
    Range("J2").Select
    Columns("I:I").EntireColumn.AutoFit
    Columns("J:J").EntireColumn.AutoFit
    ActiveWindow.SmallScroll ToRight:=2
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "EPDB Billing Addr City"
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "EPDB Billing Addr State"
    Range("M1").Select
    Columns("K:K").EntireColumn.AutoFit
    Columns("L:L").EntireColumn.AutoFit
    ActiveCell.FormulaR1C1 = "EPDB Billing Addr Zip"
    Range("N1").Select
    Columns("M:M").EntireColumn.AutoFit
    Columns("M:M").Select
    With Selection
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .MergeCells = False
    End With
    ActiveWindow.SmallScroll ToRight:=-3
    ActiveWindow.LargeScroll ToRight:=-1
    Range("D1").Select
    ActiveWindow.SmallScroll ToRight:=1
    ActiveWindow.LargeScroll ToRight:=2
    Range("D1:M1").Select
    Selection.Font.Bold = True
    Columns("E:E").Select
    With Selection
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .MergeCells = False
    End With
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .ShrinkToFit = False
    .MergeCells = False
    End With
    ActiveWindow.SmallScroll ToRight:=8
    ActiveWindow.ScrollColumn = 1
    Cells.Select
    ActiveWindow.SmallScroll ToRight:=6
    ActiveWindow.LargeScroll ToRight:=1
    ActiveWindow.ScrollColumn = 8
    Selection.Sort Key1:=Range("M2"), Order1:=xlAscending, Key2:=Range("J2") _
    , Order2:=xlAscending, Key3:=Range("I2"), Order3:=xlAscending, Header:= _
    xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("A1").Select
    Selection.EntireRow.Insert
    Selection.EntireRow.Insert
    Selection.EntireRow.Insert
    Selection.EntireRow.Insert
    Range("D3").Select
    ActiveCell.FormulaR1C1 = _
    "=CONCATENATE(""Tin: "",""E"","" "",R[3]C[-2],"" "",""Tin Owner Name: "",R[3]C[-1])"
    ActiveCell.FormulaR1C1 = _
    "=CONCATENATE(""Tin: "",""E"","" "",R[3]C[-2],"" "",""Tin Owner Name: "",R[3]C[-1])"
    Range("D3:M3").Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .ShrinkToFit = False
    .MergeCells = False
    End With
    Selection.Merge
    With Selection.Font
    .Name = "Arial"
    .Size = 12
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic
    End With
    Selection.Font.Bold = True
    Columns("A:C").Select
    Range("C1").Activate
    Selection.EntireColumn.Hidden = True
    Cells.Select
    Range("D1").Activate
    ActiveSheet.PageSetup.PrintArea = Selection.Address
    Range("D1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    ActiveSheet.PageSetup.PrintArea = "$D$1:$M$1019"
    With ActiveSheet.PageSetup
    .PrintTitleRows = "$1:$5"
    .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = "$D$1:$M$1019"
    With ActiveSheet.PageSetup
    .LeftHeader = ""
    .CenterHeader = "&""Arial,Bold""&20EPDB Billing Address Alignment Report"
    .RightHeader = ""
    .LeftFooter = ""
    .CenterFooter = "&P"
    .RightFooter = "&D Report Ran"
    .LeftMargin = Application.InchesToPoints(0.5)
    .RightMargin = Application.InchesToPoints(0.5)
    .TopMargin = Application.InchesToPoints(0.5)
    .BottomMargin = Application.InchesToPoints(0.5)
    .HeaderMargin = Application.InchesToPoints(0.2)
    .FooterMargin = Application.InchesToPoints(0.2)
    .PrintHeadings = False
    .PrintGridlines = False
    .PrintComments = xlPrintNoComments
    .PrintQuality = 600
    .CenterHorizontally = False
    .CenterVertically = False
    .Orientation = xlLandscape
    .Draft = False
    .PaperSize = xlPaperLetter
    .FirstPageNumber = xlAutomatic
    .Order = xlDownThenOver
    .BlackAndWhite = False
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = 160
    End With
    ActiveWindow.LargeScroll Down:=0
    Columns("F:F").ColumnWidth = 75.71
    Columns("F:F").EntireColumn.AutoFit
    Columns("F:F").EntireColumn.AutoFit
    Columns("F:F").ColumnWidth = 38.57
    ActiveWindow.LargeScroll Down:=23
    Range("I791").Select
    ActiveWindow.LargeScroll Down:=-23
    ActiveWindow.SmallScroll ToRight:=6
    Columns("L:L").EntireColumn.AutoFit
    Columns("M:M").EntireColumn.AutoFit
    ActiveWindow.LargeScroll ToRight:=-3
    Columns("E:E").EntireColumn.AutoFit
    With ActiveSheet.PageSetup
    .PrintTitleRows = "$1:$5"
    .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = "$D$1:$M$1019"
    With ActiveSheet.PageSetup
    .LeftHeader = ""
    .CenterHeader = "&""Arial,Bold""&20EPDB Billing Address Alignment Report"
    .RightHeader = ""
    .LeftFooter = ""
    .CenterFooter = "&P"
    .RightFooter = "&D Report Ran"
    .LeftMargin = Application.InchesToPoints(0.5)
    .RightMargin = Application.InchesToPoints(0.5)
    .TopMargin = Application.InchesToPoints(0.5)
    .BottomMargin = Application.InchesToPoints(0.5)
    .HeaderMargin = Application.InchesToPoints(0.2)
    .FooterMargin = Application.InchesToPoints(0.2)
    .PrintHeadings = False
    .PrintGridlines = False
    .PrintComments = xlPrintNoComments
    .PrintQuality = 600
    .CenterHorizontally = False
    .CenterVertically = False
    .Orientation = xlLandscape
    .Draft = False
    .PaperSize = xlPaperLegal
    .FirstPageNumber = xlAutomatic
    .Order = xlDownThenOver
    .BlackAndWhite = False
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = 160
    End With
    With ActiveSheet.PageSetup
    .PrintTitleRows = "$1:$5"
    .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = "$D$1:$M$1019"
    With ActiveSheet.PageSetup
    .LeftHeader = ""
    .CenterHeader = "&""Arial,Bold""&20EPDB Billing Address Alignment Report"
    .RightHeader = ""
    .LeftFooter = ""
    .CenterFooter = "&P"
    .RightFooter = "&D Report Ran"
    .LeftMargin = Application.InchesToPoints(0.5)
    .RightMargin = Application.InchesToPoints(0.5)
    .TopMargin = Application.InchesToPoints(0.5)
    .BottomMargin = Application.InchesToPoints(0.5)
    .HeaderMargin = Application.InchesToPoints(0.2)
    .FooterMargin = Application.InchesToPoints(0.2)
    .PrintHeadings = False
    .PrintGridlines = False
    .PrintComments = xlPrintNoComments
    .PrintQuality = 600
    .CenterHorizontally = False
    .CenterVertically = False
    .Orientation = xlLandscape
    .Draft = False
    .PaperSize = xlPaperLegal
    .FirstPageNumber = xlAutomatic
    .Order = xlDownThenOver
    .BlackAndWhite = False
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = 180
    End With
    With ActiveSheet.PageSetup
    .PrintTitleRows = "$1:$5"
    .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = "$D$1:$M$1019"
    With ActiveSheet.PageSetup
    .LeftHeader = ""
    .CenterHeader = "&""Arial,Bold""&20EPDB Billing Address Alignment Report"
    .RightHeader = ""
    .LeftFooter = ""
    .CenterFooter = "&P"
    .RightFooter = "&D Report Ran"
    .LeftMargin = Application.InchesToPoints(0.5)
    .RightMargin = Application.InchesToPoints(0.5)
    .TopMargin = Application.InchesToPoints(0.5)
    .BottomMargin = Application.InchesToPoints(0.5)
    .HeaderMargin = Application.InchesToPoints(0.2)
    .FooterMargin = Application.InchesToPoints(0.2)
    .PrintHeadings = False
    .PrintGridlines = False
    .PrintComments = xlPrintNoComments
    .PrintQuality = 600
    .CenterHorizontally = False
    .CenterVertically = False
    .Orientation = xlLandscape
    .Draft = False
    .PaperSize = xlPaperLegal
    .FirstPageNumber = xlAutomatic
    .Order = xlDownThenOver
    .BlackAndWhite = False
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = 180
    End With
    Columns("H:H").ColumnWidth = 28.29
    ActiveWindow.SmallScroll ToRight:=2
    Columns("I:I").ColumnWidth = 27.71
    ActiveWindow.SmallScroll ToRight:=1
    Columns("J:J").ColumnWidth = 24.86
    ActiveWindow.SmallScroll ToRight:=2
    ActiveWindow.LargeScroll ToRight:=-2
    Range("D5").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideHorizontal)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    Range("D1").Select
    ActiveWindow.LargeScroll ToRight:=1
    Range("D1:M4").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range("D6").Select
    Range("D1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    ActiveSheet.PageSetup.PrintArea = "$D$1:$M$1019"
    ActiveWindow.SmallScroll ToRight:=1
    Range("E5").Select
    With ActiveSheet.PageSetup
    .PrintTitleRows = "$1:$5"
    .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = "$D$1:$M$1019"
    With ActiveSheet.PageSetup
    .LeftHeader = ""
    .CenterHeader = "&""Arial,Bold""&20EPDB Billing Address Alignment Report"
    .RightHeader = ""
    .LeftFooter = ""
    .CenterFooter = "&P"
    .RightFooter = "&D Report Ran"
    .LeftMargin = Application.InchesToPoints(0.5)
    .RightMargin = Application.InchesToPoints(0.5)
    .TopMargin = Application.InchesToPoints(0.5)
    .BottomMargin = Application.InchesToPoints(0.5)
    .HeaderMargin = Application.InchesToPoints(0.2)
    .FooterMargin = Application.InchesToPoints(0.2)
    .PrintHeadings = False
    .PrintGridlines = False
    .PrintComments = xlPrintNoComments
    .PrintQuality = 600
    .CenterHorizontally = False
    .CenterVertically = False
    .Orientation = xlLandscape
    .Draft = False
    .PaperSize = xlPaperLegal
    .FirstPageNumber = xlAutomatic
    .Order = xlDownThenOver
    .BlackAndWhite = False
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = 180
    End With
    Columns("G:G").ColumnWidth = 17.57
    ActiveWindow.SmallScroll ToRight:=-2
    Range("G4").Select
    Selection.EntireRow.Insert
    Range("G5").Select
    ActiveCell.FormulaR1C1 = "EPDB Billing Address"
    Range("G6").Select
    ActiveCell.FormulaR1C1 = "Number"
    Range("G5:M5").Select
    ActiveWindow.LargeScroll ToRight:=-2
    Range("G5").Select
    Selection.ClearContents
    Rows("5:5").Select
    Range("D5").Activate
    Selection.Delete Shift:=xlUp
    Range("G5").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "EPDB Billing Addr #"
    Range("H9").Select
    Columns("G:G").ColumnWidth = 18.29
    Columns("G:G").ColumnWidth = 18
    Range("D3:M3").Select
    ActiveCell.FormulaR1C1 = _
    "=CONCATENATE("" Tin: "",""E"","" "",R[3]C[-2],"" "",""Tin Owner Name: "",R[3]C[-1])"
    Range("D4").Select

    End Sub

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks it works great with Application.ScreenUpdating = False

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Chippenham, UK
    Posts
    136
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-13 12:46, ap1 wrote:
    Thanks it works great with Application.ScreenUpdating = False
    Don't forget to put Application.ScreenUpdating = True

    at the end


    Regards,

    Gary Hewitt-Long

  6. #6
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    At the end of the macro control is returned to Excel. This automatically sets screenupdating back to true.

    The only time I've had problems with screenupdating is when I've set it to false before displaying a userform.

    Create a UserForm1 and try this for an example:


    Public Sub main()

    Application.ScreenUpdating = False
    UserForm1.Show

    End Sub


    Now drag the useform all over the screen.

    _________________
    [b] Mark O'Brien

    [ This Message was edited by: Mark O'Brien on 2002-03-13 13:38 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com