Keping a UserForm open as VBA runs

  • Thread starter Thread starter Chris Gorham
  • Start date Start date
C

Chris Gorham

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
 
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,

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

Private Sub UserForm_Activate()

TextBox1.Text = "Hello"

'do something

End Sub




Mike Tomasura
 
Back
Top