Vba screen updating status bar

Rated 4.16/5 based on 828 customer reviews

While your visual basic procedures are running it is helpful to give users some information about what is happening during any apparent pauses caused by data processing.

Messages can be displayed in the gray bar at the bottom of the Access program window, controlled by a VBA procedure.

I’m sure there are ways to make this run faster, but for this exercise we’ll just focus on how to show a message in the status bar. Value Case Is As I said earlier, it’s easy to add a few messages in the status bar, so you can keep an eye on the macro’s progress.

To test the code, you can create a workbook with numbers in cells A2: A100000. The extra bit of code might slow the macro down a little more, but it’s usually a good tradeoff – a few seconds slower, for a little peace of mind.

Even inexperienced VBA coders, that write their own code, often and up with the same problem due to their code being full of Select, Selection, Activate etc.

Usually the screen updating is turned off when a macro runs, so it’s hard to tell if anything is happening.

You can use it to display the progress of a VBA procedure, or indicate that the procedure is complete.

The code to put a message on the status bar is shown below.

Screen Updating=False This will stop Excel having to repaint the screen each time the macro does something.

To see the huge improvement that can be obtained, run the 2 macros below via Tools Sub Slow With Flicker() Dim r Cell As Range For Each r Cell In Range("A1: A15000") r Cell.

Leave a Reply