Progress Window (2000)

Topic

New Reply

Thanks to all for your help, notably, Legare and Andrew. I am two weeks ahead of schedule on this project thanks to you guys. I’m learning visual basic as I go along. At any rate,

I’m at a stage where i need to generate some sheets that take a while. With screen updating off, it looks like the system has hung while it generates. I want to show a status/progress screen while the macro is operating. I’ve tried Showing a userform, but the macro does not process until the dialog box is closed.

Also, using InputBox, how do I run a macro when “cancel” is clicked?

Thanks again, all.

Reply | Quote

Replies

You can try j-walk.com, where a method of implementing a progress bar is shown, and a sample workbook is available for download.

There are 2 distinct implementations of an InputBox for Excel, the VBA variety and the Excel variety (Application.InputBox), which is more versatile. The former returns an empty string if Cancel is clicked (the same result as the OK button with no input). The latter variety returns 0 (false) which is also the value when the OK button is clicked without any input. So depending on what you are doing, you might be better using a UserForm.

Andrew C

Reply | Quote

One simple way to show status for a long operation is to use the Excel status bar. You can display a message in the status bar that shows what is happening and how it is progressing. Something like:

    Application.StatusBar = "Building sheet 1."

Use something like this to run a macro when cancel is pressed on an input box:

Dim vResult as variant
    vResult = InputBox ("Enter value")
    If vResult = False Then
        Call MyMacro
    End If

Reply | Quote

That’s it, Legare. I didn’t know you you could use the return value of an InputBox as a string and a boolean at the same time. Hmm.

Reply | Quote

That’s why it has to be defined as a variant.

Reply | Quote

I’ve got a few spreadsheet that do some complicated stuff and need some form or ‘Don’t worry, I’m still working’ note to the user. You can create a progress form and display it modeless (or is it modal?). Use something like this …

frmProgress.Show 0

and the code keeps progressing. Then in your code you can update the frmprogress with something like this …

frmProgress.lblProgress.Caption = “message”

You might need a repaint commant too. You can also do this in excel 97 – there was an article in the knowledge base that showed how do do it but i’ve forgotten the number and cannot find it now

Cheers,

Tim

Reply | Quote

Tim

The Show 0 thing works like a charm. Thanks for ALL your help. My supervisors are impressed that I started this project not knowing VB (only C++) and now it’s looking pretty slick in about 2 weeks time. I owe it all to everybody here.

Reply | Quote