Updating a cell during VBA execution

[Mr930]

I have some lengthy VBA loops, and I am trying to display a counter in a cell. In each loop I have it place the counter in the cell. It works fine and updates as long as Excel has focus. However, if I go to another application, the cell is no longer updated, until the loop finishes and the Excel screen is updated.

Is there a way to have the cell be updated even if Excel loses focus?

thanks
Fred

 

[Jaafar Tribak]

Try using DoEvents inside the loop.

Regards.

 

[njimack]

I find the trouble with placing a counter in a cell is that you need Screenupdating set to True which, in most cases, will slow down the execution of the code.

I usually inform the user of the macros progress via the statusbar. As far as I know, this will continue to update even if Excel loses its focus (I could be wrong tho')

 

[Mr930]

How do you go about putting information in the status bar?

thanks
Fred

 

[VoG]

e.g.

Application.Statusbar = "30% done"

At the end

Application.Statusbar = False

 

[njimack]

How do you go about putting information in the status bar?

In terms of informing the user of the progress, I usually use a counter, e.g. (this would be within a Loop)

Application.StatusBar = "Finding policy " & n - 1 & " of " & End_Row - 1 & "..."

where n = row currently being processed, End_Row = end of data