rgmatthes
04-02-2010, 01:34 PM
Hi again,
You guys responded to my question yesterday (teaching me about DoEvents!), so I'm hoping you can help me once more. : pray2:
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... :banghead:
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!
:beerchug:
You guys responded to my question yesterday (teaching me about DoEvents!), so I'm hoping you can help me once more. : pray2:
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... :banghead:
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!
:beerchug: