Problems with interface design for when long macros process (suggestions welcome!)

[rgmatthes]

Hi again,

You guys responded to my question yesterday (teaching me about DoEvents!), so I'm hoping you can help me once more.

I have a macro that takes about three minutes to run. My goal is to provide the user with something that looks better than a blank, stalled window for three minutes while everything's working in the background.

Here's how my macro works:

First the user fills out a form with some data and clicks OK. The data is used to pre-populate a second form, which the user completes. When the user clicks "OK" on the second form, a third form activates. Stored in userform_activate() in the third form are calls to some productivity subroutines, which go to work on the open files. The subs take three minutes to finish. After that, a label on form 3 displays some stats on how successful the macro was, and an exit button is made visible for the user.

Here's where I hit a wall. What's good practice for a "waiting" interface while the subs execute? I can't seem to make anything work.

First, I tried making it so the label on form 3 updated with stats while the subroutines ran... but the form didn't stay "on top" of the open files, so the user couldn't see it. I found some code to force form 3 to "Always on Top", but then it stayed on top across all open apps... and the label wasn't able to update itself anyway, for some reason (just showed as "not responding").

Then I tried making form 3 Modal... but the subroutines wouldn't run. The form would just open and sit there 'til I x'ed it. THEN my subs started again. I don't know enough about Modality to make this work, it seems, but this may be the best approach.

When I tried minimizing the files the subs work on, form 3 seemed to disappear. Is there a way to force the form to stay Restored and visible while the files my macro is working on stay minimized? I'm also worried that form 3 will just look unresponsive again.

I also tried hiding the open files by setting their visibility to false when they're opened, but then my macro can't reference them and work on them! (Frustration setting in...)

Setting screenupdate to false sounds like it could help if I did it that before I opened the files to be worked on, thereby keeping them "invisible" but still enabling them to be referenced. Problem is, I can't make changes to the statusbar to update the user about what's going on because the statusbar doesn't update while it's supposed to because the program seems to hang when I try this...



So please - ANYTHING you could think of to help would be really appreciatied, as it feels like I've tried everything!

ONE CAVEAT: Obviously I've tried a bunch of strategies here, but I haven't RETRIED them all with DoEvents - so if you think it's work spending an hour to rewrite something I thought had failed, I'll definitely do it... but I'm not going to try all these again if I don't have to!

THANKS AGAIN, GUYS!

[lucas]

Would a statusbar update be an option?

http://www.vbaexpress.com/kb/getarticle.php?kb_id=993

[GTO]

I also tried hiding the open files by setting their visibility to false when they're opened, but then my macro can't reference them and work on them! (Frustration setting in...)

Hi there,

I could be wrong, but everything from the userform not 'responding' (which I took to mean updating) to "...my macro can't reference them..." seems to indicate that 'stuff' is not being qualified well. That is, that there may be a dependancy upon that which is active.

I personally do not see a bunch of DoEvents being likely to speed things up.

I hate to say it, but as your project appears to have multiple files involved, and no doubt too many things going on to post said files... I would think the most likely way of getting good help would be to replicate the files and macro, albeit in a much smaller way, to show us the logic and methods currently employed.

I hope that makes sense and that I am not mis-directing you.

Mark

[Paul_Hossler]

lucas's reference has some nice features

Here's a WB with the subs that I like to use. Maybe you'll get some ideas.

ProgressInit() sets up the display form, message, total, and the name of a sub to call while displaying a progress bar. I usually have it call the series of subs that get a progress bar (ONE, TWO, and THREE in this)

This is a driver example of calling 3 subs, each with a progress bar while the macro actually continues processing

[vba]
Sub drv()
Const gErrOne As Long = 1
Const gErrTwo As Long = 2
Const gErrThree As Long = 3

'demo setup ---------------------------------------
ActiveSheet.Cells(1, 1).CurrentRegion.Clear

'no ScreenUpdating = False since I want to show activity in demo

'setup parameters and then call the calculation sub = ONE, TWO,THREE
'528 is just a number for the total
'demo setup ---------------------------------------

'Function ProgressInit(Msg As String, TotalNumber As Long, _
' SubToRun As String, _
' Optional CancelMsg As String = "Cancel Pressed", _
' Optional Title As String = "Please Wait ...") As Boolean
On Error GoTo ErrHandler
If Not ProgressInit("Part 1 ... please wait", 528, "ONE", "Part 1 is not finished", "Part 1 of 3") Then
Call Err.Raise(vbObjectError + gErrOne, "ProgressDemo", "Cancel Pressed")
End If
If Not ProgressInit("Part 2 ... keep waiting", 528, "TWO", "Part 2 has more to go", "Part 2 of 3") Then
Call Err.Raise(vbObjectError + gErrTwo, "ProgressDemo", "Cancel Pressed")
End If
If Not ProgressInit("Part 3 ... almost there", 528, "THREE", "Part 3 is still running", "Part 3 of 3") Then
Call Err.Raise(vbObjectError + gErrThree, "ProgressDemo", "Cancel Pressed")
End If

MsgBox "All Done"
Exit Sub

ErrHandler:
Select Case Err.Number
Case vbObjectError + gErrOne
MsgBox "Canceled in Part One"
Case vbObjectError + gErrTwo
MsgBox "Canceled in Part Two"
Case vbObjectError + gErrThree
MsgBox "Canceled in Part Three"
Case Else
MsgBox "Unhandleded Error"
End Select
End Sub
[/vba]

Paul