Keping a UserForm open as VBA runs
 

Hi,

I'm designing a macro that takes a few seconds to run on
big sheets. I'd like to update the user on progress. I
could do this via the statusbar, but I'd prefer to have a
pop userform confirming the tasks as they complete.

the userform's I've developed before have to be hidden
before the code will run - how do I keep it open while the
code runs in the background..??

thks..Chris

Keping a UserForm open as VBA runs
 

What you want to do is use a Modeless form (Excel 2000 +)
I've created a userform (UserForm1) and inserted a label (Label1)

Then in Module1:

Sub testit()
Dim frm As UserForm1, dtmStart As Date

Set frm = New UserForm1
frm.Show vbModeless

frm.Label1 = "Doing wait for 3 seconds"
frm.Repaint
dtmStart = Now()
Do Until dtmStart + TimeValue("00:00:03") <= Now(): Loop

frm.Label1 = "Doing wait for 5 seconds"
frm.Repaint
dtmStart = Now()
Do Until dtmStart + TimeValue("00:00:05") <= Now(): Loop

frm.Label1 = "Finished"
End Sub


"Chris Gorham" wrote in message
...
Hi,

I'm designing a macro that takes a few seconds to run on
big sheets. I'd like to update the user on progress. I
could do this via the statusbar, but I'd prefer to have a
pop userform confirming the tasks as they complete.

the userform's I've developed before have to be hidden
before the code will run - how do I keep it open while the
code runs in the background..??

thks..Chris

Keping a UserForm open as VBA runs
 

Chris,

The general technique is to show the form, then initiate your code from
within there, updating the progress bar as you go.

John Walkenbach shows how at

http://j-walk.com/ss/excel/tips/tip34.htm

He also has a more complex status bar progress bar.

http://j-walk.com/ss/excel/files/developer.htm



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Chris Gorham" wrote in message
...
Hi,

I'm designing a macro that takes a few seconds to run on
big sheets. I'd like to update the user on progress. I
could do this via the statusbar, but I'd prefer to have a
pop userform confirming the tasks as they complete.

the userform's I've developed before have to be hidden
before the code will run - how do I keep it open while the
code runs in the background..??

thks..Chris

Keping a UserForm open as VBA runs
 

Chris,

You can put the code from your macro into a userform.

Private Sub UserForm_Activate()

TextBox1.Text = "Hello"

'do something

End Sub




Mike Tomasura
"Chris Gorham" wrote in message
...
Hi,

I'm designing a macro that takes a few seconds to run on
big sheets. I'd like to update the user on progress. I
could do this via the statusbar, but I'd prefer to have a
pop userform confirming the tasks as they complete.

the userform's I've developed before have to be hidden
before the code will run - how do I keep it open while the
code runs in the background..??

thks..Chris