Preventing Excel screen action during a VBA routine

[popper]

Hi

I have written some VBA code which occasionally needs to sort data in a hidden worksheet. I find that I need to unhide the sheet before the sort can occur and then rehide it. The sort action causes the screen to flicker and jump around where most of the rest of the routine is pretty smooth. Is there a way of inhibiting all this disctracting movement. I recall in the old days there was a Lotus macro command that will stifle the action during the running of a macro. Is there an equivalent in Excel? I would appreciate any suggestions that would alleviate this problem.

With thanks

 

[misterstick]

1. stop using ActiveSheet

2. Application.ScreenUpdating = False

3. be really careful



mr s. <

 

[HughLerwill]

Application.ScreenUpdating Property
True if screen updating is turned on. Read/write Boolean

 

[SkipVought]

popper,

It is NOT necessary to unhide your sheet. It is your code that has incorrect range references...

[b]
    Sheets("HiddenSheet").[/b]Range("A1").Sort _
        Key1:=[b]Sheets("HiddenSheet").[/b]Range("A2"), Order1:=xlAscending, _
            Header:=xlYes, _
            OrderCustom:=1, _
            MatchCase:=False, _
            Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[newuser08]

I used Application.Calculation = xlCalculationManual
with a frm showing 'workng please wait'

when the process is complete hide the frm and turn the calculation back on.

 

[popper]

Thank you all. The screenupdating property worked fine. The "hiddensheet" solution is what I first tried without success. Perhaps I should try again.


The manual calculation option will be valuable when the system is actually executing length and complex spreadsheet formulae.

Three great responses. Thanks again.

P