Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Excel 97: Turn Display Off During Macro Operation

    Is there a way to turn off the display while executing a macro? I have recorded a lengthy macro that creates several files for e-mailing, but the shifting display (print preview, page break-on then off, then print preview and so on) can be dizzying.
    Also, how can I display some message to the user like "Preparing files for e-mailing" or something to let them know that a macro is running after I've turned off the display.
    Finally, what is the command to turn the display back on at the end of the macro?
    Thx in adv.

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 97: Turn Display Off During Macro Operation

    Are they display boxes? if so, try the following:

    Application.DisplayAlerts = False
    'Your code goes here...
    Application.DisplayAlerts = True

    Can't help you if they're actual windows tho..
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Excel 97: Turn Display Off During Macro Operation

    They are windows. The macro I've recorded steps through copy-paste values, deletion/hiding of rows, print range selection, previews, page-break previews, normal views and so on.
    I've already added DisplayAlerts=False so the user isn't required to hit Enter 15 (or so) times to overwrite all the files created by the macro.
    I have no experience with Dialog boxes and none have been included here. Eventually, I would like to have one here informing the user, but that's not as important yet as turning the display off.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Excel 97: Turn Display Off During Macro Operation

    Hi,
    I think you're after Application.ScreenUpdating = True or Application.ScreenUpdating = False. Your best bet for displaying a message would either be a userform or set the Application.StatusBar to whatever text you want displayed.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 97: Turn Display Off During Macro Operation

    I'd also be looking at the cutting/pasting which causes or the swapping around to begin with.

    If you did something like:

    Dim rngFrom As Range
    Dim rngTo As Range
    Set rngFrom = ActiveSheet.Range(Cells(1, 1), Cells(3, 5))
    Set rngTo = ActiveSheet.Range(Cells(255, 1), Cells(257, 5))
    rngFrom.Copy rngTo

    It would avoid a lot of the swapping round, and would probably execute heaps faster.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Excel 97: Turn Display Off During Macro Operation

    Thanks ScreenUpdating was just what I needed. Would you provide an example of how to use StatusBar? I am looking to alert a user that a macro is running in the background.

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Excel 97: Turn Display Off During Macro Operation

    Personally, I'd do two things:
    Screen.MousePointer = 11
    Application.Statusbar = "Preparing emails. Please wait..."
    to change the cursor to an hourglass and change the statusbar text, then reset them at the end with:
    Screen.MousePointer = 0
    Application.Statusbar = False
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 97: Turn Display Off During Macro Operation

    Aren't these guys great!? )
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Excel 97: Turn Display Off During Macro Operation

    To: gwhitfield
    This looks like what I should be using instead of poking around from one row to the next, switching ranges left and right. The macro takes a standardized report and trims it 15 different ways to be mailed to various departments. Occassionally, additional row(s) are added (or subtracted), causing a rewrite to the macro. I'll experiment with rngFrom/rngTo once I get this mod checked out. Bet it is faster, too. Thx.

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Excel 97: Turn Display Off During Macro Operation

    To: Rory
    I just tried the ScreenPointer = 11, but get an error message. I moved ScreenPointer to the start of the macro BEFORE Application.DisplayAlerts = False and Application.ScreenUpdating = False. I get an "object required" error and the ScreenPointer = 11 is highlighted in the VBEditor. What do you suggest I should try next.

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Excel 97: Turn Display Off During Macro Operation

    Oops, that's an Access thing - you would need to add a reference to Access in your project before it will work. Try Application.cursor = xlWait and Application.cursor = xlDefault. Sorry about that!
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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