Application.Screenupdating (Excel 2000)

Topic

New Reply

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?

Reply | Quote

Replies

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!

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote