Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Mar 2001
    Location
    Indiana, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Application.Screenupdating (Excel 2000)

    I have an application that does a lot of screen manipulation. It was running slow, so I put some Application.Screenupdating = False statements in the update modules. However, it fails to turn the screen updating off. You can still see every update the code is doing. I thought I remembered reading somewhere that the screen updating is automatically turned back on at the end of a procedure or module even if you don't explicitly turn the updating back on (= True).

    Is there any way to get the screen updating turned off for the duration of the application?

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Application.Screenupdating (Excel 2000)

    I'm looking in a VBA reference book and it says: "...if your macro writes information to a worksheet, turn off screen updating by using Application.ScreenUpdating=False.

    The example given shows the line Application.ScreenUpdating=True as the last line before End Sub.

    I'm not an authority on VBA but I did stay at a Holiday Inn Express last night!
    - Ricky

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Application.Screenupdating (Excel 2000)

    Once you turn Screen Updating off, it should stay off until you turn it off or until the current macro execution exits. That is not an exit from a Sub Procedure, but an exit back to Excel from the top level procedure.

    Could you copy and paste the code you are using to turn Screen Updating off into a message? It sounds like something is mispelled and it is not actually getting turned off.
    Legare Coleman

  4. #4
    New Lounger
    Join Date
    Mar 2001
    Location
    Indiana, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Application.Screenupdating (Excel 2000)

    Here is the code that is kicking off the processing for my application. I was thinking the problem might be due to the "Private Sub", but I changed it to a "Public Sub" and it still did not work. The code is being executed from a command button on the main worksheet.

    Private Sub btn_Run_Compute_Click()

    Application.ScreenUpdating = False

    Call Clear_Update_List

    Sheets("Compute").Select
    ActiveSheet.Cells(15, 8).Select

    Call Data_File_Search

    Application.ScreenUpdating = True

    End Sub 'btn_Run_Compute_Click

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Application.Screenupdating (Excel 2000)

    Mark,

    I think it would be helpful to know what goes on in Clear_Update_List and Data_File_Search. Could you post those routines or at least give a summary of what they do. Do they set ScreenUpdating on or off ?.

    Andrew C

  6. #6
    New Lounger
    Join Date
    Mar 2001
    Location
    Indiana, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Application.Screenupdating (Excel 2000)

    The only time I turn ScreenUpdating on and off is in the code that I attached earlier. It was my understanding that when I turned it off at the start of code execution, that it would stay off until I turned it back on or the entire macro ended. It appears that every sub procedure that the code enters, turns the ScreenUpdating back on. In desperation, I tried to put just the ScreenUpdating = False statement into every sub procedure, but that didn't work either. Am I missing the essentials on how the ScreenUpdating command works?

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Application.Screenupdating (Excel 2000)

    I don't see any reason why that would not work without seeing what is in the other procedures. Also, do you have any event procedures that might be turning it back on?
    Legare Coleman

  8. #8
    New Lounger
    Join Date
    Mar 2001
    Location
    Indiana, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Application.Screenupdating (Excel 2000)

    Here is a brief synopsis of what the code is doing. It opens a data file transmitted from the mainframe, it cycles through the different corporate divisions, copies the data for each division and opens that divisions report file. It pastes the data for the current month into the report files and updates a couple pivot tables. There are no other event procedures in the code.

    I have another application that I cloned from this one and the ScreenUpdating works fine. I am beginning to wonder if the spreadsheet is corrupt.

Posting Permissions

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