Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Freeze display (2000 SR1)

    Some of the macros that I am writing have the display flashing this or that, flipping between windows, etc. When I was writing macros for Lotus, there was a command to freeze the display, not updating anything on screen until the freeze was lifted or the macro finished running. In addition to eliminating unnecessary visual distractions, the macro would also run faster. Is there a similar command that could be added to a VBA macro?

  2. #2
    New Lounger
    Join Date
    Dec 2000
    Location
    Alberta, Canada
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Freeze display (2000 SR1)

    David

    Here are some code snippets I use...

    Setting calculations to manual will keep Excel from updating any "dependant" cells until after your vba code is finished running... this may or may not have a huge effect on the speed of your vba code.

    ' Set Calculations to Manual
    ' Create a variable to save the user preference setting
    Dim saveCalculation As String
    ' Save the users setting for the xlCalculation
    saveCalculation = Application.Calculation
    ' Turn off calculations... speeds up VBA code
    Application.Calculation = xlCalculationManual
    ' When done... reset back to user preferrence
    Application.Calculation = saveCalculation

    Turning off screen updating sounds like exactly what you were asking about. Be aware though, ALL Excel screen updates are disabled by the following code, which can make your application appear to freeze. I always use some other activitiy indicator in conjunction with disabling screen updates... like setting a message in the status bar to "Please wait..." and, for long routines, updating the status bar message every so often throughout my code.

    ' Set StatusBar On & send a message
    ' Create a variable to save the user preference setting
    Dim saveStatusBar As String
    ' Save the users setting for the StatusBar
    saveStatusBar = Application.DisplayStatusBar
    ' Turn on the StatusBar
    Application.DisplayStatusBar = True
    Application.StatusBar = "This will take some time. Please be patient..."

    ' Turn off screen updates... makes Excel appear to freeze!!!
    Application.ScreenUpdating = False

    '''' Run your time consuming routine here

    ' Turn screen updates back on
    Application.ScreenUpdating = True

    ' Send another message
    Application.StatusBar = "Processing complete. Thank you for your patience..."

    ' Set the StatusBar back to the user preferrence
    Application.DisplayStatusBar = saveStatusBar

    BTW... love your signature! (a fellow Canadian)

    Hope this helps...

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Freeze display (2000 SR1)

    <img src=/S/trophy.gif border=0 alt=trophy width=15 height=15> The "Application.ScreenUpdating = False" is exactly what I had in mind. For now, the processes are not long or time consuming, just very busy visually. This should make things a little more to my liking. (My opinion of programming is "Let them see the input screen and the final output -- hide everything else.")

  4. #4
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Freeze display (2000 SR1)

    I have a userform that is shown while macros run. The userform is attached (.bmp file) as an example.

    <pre>Sub PopulateAvail()
    Application.ScreenUpdating = False

    Load frmAvailProg
    frmAvailProg.Show vbModeless

    frmAvailProg.lblAvailProg.Caption = "Formatting...."
    frmAvailProg.Repaint
    AvailFormat

    frmAvailProg.lblAvailProg.Caption = "Filling in Dates...."
    frmAvailProg.Repaint
    AvailDateFill

    frmAvailProg.lblAvailProg.Caption = "Filling in Hours and Minute Intervals...."
    frmAvailProg.Repaint
    AvailHourIntervalFill

    frmAvailProg.lblAvailProg.Caption = "Unit Labels are being written...."
    frmAvailProg.Repaint
    FillUnits

    frmAvailProg.lblAvailProg.Caption = "Inserting Formulas...."
    frmAvailProg.Repaint
    FillFormulas

    frmAvailProg.lblAvailProg.Caption = "Cleaning up...."
    frmAvailProg.Repaint
    CleanUpAvail

    Unload frmAvailProg
    Application.ScreenUpdating = True
    End Sub
    </pre>

    Attached Images Attached Images

  5. #5
    New Lounger
    Join Date
    Dec 2000
    Location
    Alberta, Canada
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Freeze display (2000 SR1)

    Very slick! (and professional) <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

    Thanks for sharing, it's given me a great idea for my own version.

  6. #6
    New Lounger
    Join Date
    Dec 2000
    Location
    Alberta, Canada
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Freeze display (2000 SR1)

    David

    I stole your signature... <img src=/S/devil.gif border=0 alt=devil width=15 height=15>

Posting Permissions

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