An easy way to lock the screenupdating and show a userform?

[Jaymond Flurrie]

I have a program which runs for 77 minutes. I need to display a screen in style of "counting..." when user presses a "run" button and then run thru this program and once it is finished, display the main form (the one user pushed the "run" button from).

The problem is that I don't know how to keep that form up no matter what the program does. I have Application.ScreenUpdating = False
set thru the whole run but still what Excel happily does, is to show "Not responding" and have absolutely no idea of my "counting..." form.

I clearly have to same time both use and avoid using me.hide from my "counting..."-form, but what's the golden middle road here? How do I both keep it up and keep running the code? Is a form the wrong solution? Should it be MsgBox? Would it change anything? I don't want to have any "ok"-button there.

Should be simple, but I have absolutely no idea how to implement this with VBA.

 

[Rasm]

Use the statusbar function - notice the loop 'i' - so I have a counter when it is >1000 - I show status - then reset counter - this is inside the loop - will that work for you

If AAAlong > 1000 Then
                Application.StatusBar = "Computing detection limit (" & i & ")"
                AAAlong = 1
End If
 AAAlong = AAAlong + 1

 

[Rasm]

ohhh the Exel statrusbar is shown right above the windows START

 

[Jaymond Flurrie]

Use the statusbar function - notice the loop 'i' - so I have a counter when it is >1000 - I show status - then reset counter - this is inside the loop - will that work for you

If AAAlong > 1000 Then
                Application.StatusBar = "Computing detection limit (" & i & ")"
                AAAlong = 1
End If
 AAAlong = AAAlong + 1

But this still shows "Not responding" and not any form, right? I have already implemented the
Application.StatusBar =
thru my program, but still I fear that as soon as user sees "Not responding" he panics and presses the reset button of the computer. One option would be to show a sheet that says "yeah, I actually do something while you stare at this", but is there really no way to do this with a form?

 

[Jaymond Flurrie]

Even better would be if there would be some cancel button, but is that impossible too?

 

[shg]

Can you back up a few steps and describe what you're doing from 10,000 feet?

You can use a progress meter (Andy Pope has a nice collection) if you have some loop counter that you can use. Every so many iterations, you update the progress meter.

When you do that, you will typically need to repaint the (progress meter) form.

You can add a cancel button that set a variable that you can test periodically.

So you might have something like this:

for i = 1 to aLot
   if (i mod 1000) = 0 then
        doevents ' process the cancel button
        if bQuit then exit sub
        myProgressForm.ShowProgress i/aLot
        myProgressForm.repaint
    endif
 
    [... loop processing ...]
next i

 

[Jaymond Flurrie]

Can you back up a few steps and describe what you're doing from 10,000 feet?

You can use a progress meter (Andy Pope has a nice collection) if you have some loop counter that you can use. Every so many iterations, you update the progress meter.

When you do that, you will typically need to repaint the (progress meter) form.

You can add a cancel button that set a variable that you can test periodically.

So you might have something like this:

for i = 1 to aLot
   if (i mod 1000) = 0 then
        doevents ' process the cancel button
        if bQuit then exit sub
        myProgressForm.ShowProgress i/aLot
        myProgressForm.repaint
    endif
 
    [... loop processing ...]
next i

Basically I have a sheet of now 3 million, soon 80 million cells that needs to be processed in a certain way. That takes time and it works, but the problem is that I want my user to know that it really does work and no, it didn't freeze.

But I think this repaint might answer to my needs, thanks!

 

[Rasm]

This is kind of a dum suggestion - but as you update the Statusbar - also write a value to the sheet - i.e. cell "AAA1" - set value to 1 - next time you update the statusbar write value zero - I think as long as you make a change to a cell in a sheet the "Not responding" does not show. So this may be a way to fool MS.

 

[Jaymond Flurrie]

This is kind of a dum suggestion - but as you update the Statusbar - also write a value to the sheet - i.e. cell "AAA1" - set value to 1 - next time you update the statusbar write value zero - I think as long as you make a change to a cell in a sheet the "Not responding" does not show. So this may be a way to fool MS.

I think this is worth of trying. Thanks for the hint!

 

[Rasm]

I did not try this - but give it a try


http://www.your-save-time-and-impro...nline-resource.com/excel-vba-progressbar.html