VBA Excel - form focus confusion

[rgraylint]

I have an Excel VBA based conversion app. A conversion settings form (A) pops on startup. I change some settings and click a <Start Conversion> button to start the conversion process. The conversion works fine, but I want to add a status dialog to tell me where it is in the process.

I added a simple status form (B) with no code and one label control. My plan is to load different status text in the label during the conversion and show the status form. I'm using this code:

frmStatus.lblStatusMsg.Caption = "Building column sequence..."
frmStatus.Show

The conversion appears to hang at this point. If I hit <Pause> on the debugger the "frmStatus.Show" statement shows as currently active. Apparently when the status form displays it get focus even though there is no code on the status form.

Can you tell me how to display a status form (B) like this but allow my parent form (A) code to continue?

If there is some completely alternate solution involving some other type of form or whatever, I'd be interested in hearing about that as well.

Thanks in advance!

 

[ettienne]

Forms can be modal or modeless. Modal forms stop all processing until the form is closed, and this is the default. Modeless forms do not stop processing, and are therefor used to display progress or a status.

You need to use
frmStatus.Show vbModeless

You may run into a problem, like a modeless form cannot be started from a modal form, in which case it would be easier to place your status label on form A and get rid of the other form.

 

[rgraylint]

Thanks so much for the reply. For some reason my code just to my error routine right when it hits this line of code.
frmStatus.Show vbModeless

I suspect it is related to your point about a modal form starting a modeless one. I'll have to do some research and see what's up with that some time.

In the meantime your suggestion to use a status label on my main form is a good one. Sounds easy and will look just fine. Think I'll do that.

Thanks again for your help!

 

[rgraylint]

Whoops! I have one more question on this. I've setup my main form with a status label. I update that directly and have killed my 2nd status form.

Problem is the status text won't update on the main form. I've tried:

' update status display
frmMain.lblStatusMsg.Caption = "Converting record: " & i & "of " & EndRow & "..."
frmMain.Refresh ' APPARENTLY Refresh METHOD DOESN'T EXIST IN EXCEL

DoEvents() ' DOESN'T EXIST IN EXCEL EITHER

' update status display
frmMain.lblStatusMsg.Caption = "Converting record: " & i & "of " & EndRow & "..."
frmMain.RePaint ' This does work, sort of. The screen flashes so badly you can't read the count. Plus the repaints slow the process by about 600%!

So how the heck are you supposed to refresh a form or control in Excel?

Thanks again for your help!

 

[PHV]

Why are you saying that DoEvents doesn't exist in Excel ?

http://support.microsoft.com/kb/118468

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rgraylint]

Ahh. Thanks for the link. I come from the old DOS C days and I can't bring myself to make a function call without parens. I was trying to use: "DoEvents()" and was getting an error.

According to the article you reference you don't use the "()". Sorry for the newbie mistake.

And the DoEvents call DID fix my screen update problem.

Thanks so much for all your help guys!