How can I run a macro in the background whilst a UserForm is visib

C

cdb

I have a workbook with an update button on (which has a macro behind it).
When I press this button I want a userform to open and display a flashing
message for the duration of the macro. I have tested all the bits seperately
and they all work fine - the update macro by itself works and updates the
data and the userform opens and display a flashing message if opened by
itself.

The problem I have is, if I put the userform into the update macro, once I
click the button the userform displays, but none of the code in the macro
works. I have tried adding the DoEvents code in but this doesn't seem to help.

Can anyone help me?

Code below
-------------------------------------------------------------------------------------------------
Sub UpdateData()
DoEvents
UserForm1.Show
~ My Code
UserForm1.Hide
End Sub

------------------------------------------

Private Sub UserForm_Activate()
Label1.Visible = False
StartLoop:
If Label1.Visible = False Then Label1.Visible = True Else Label1.Visible =
False
RunPause
GoTo StartLoop
End Sub

------------------------------------------

Sub RunPause()
PauseTime = 0.75 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.
Loop
End Sub
 
J

Jim Rech

Unless you make the form's ShowModel property false this is true:

UserForm1.Show
MsgBox "You won't see me until the form is unloaded/hidden"


--
Jim Rech
Excel MVP
|I have a workbook with an update button on (which has a macro behind it).
| When I press this button I want a userform to open and display a flashing
| message for the duration of the macro. I have tested all the bits
seperately
| and they all work fine - the update macro by itself works and updates the
| data and the userform opens and display a flashing message if opened by
| itself.
|
| The problem I have is, if I put the userform into the update macro, once I
| click the button the userform displays, but none of the code in the macro
| works. I have tried adding the DoEvents code in but this doesn't seem to
help.
|
| Can anyone help me?
|
| Code below:
| -------------------------------------------------------------------------------------------------
| Sub UpdateData()
| DoEvents
| UserForm1.Show
| ~ My Code
| UserForm1.Hide
| End Sub
|
| ------------------------------------------
|
| Private Sub UserForm_Activate()
| Label1.Visible = False
| StartLoop:
| If Label1.Visible = False Then Label1.Visible = True Else Label1.Visible =
| False
| RunPause
| GoTo StartLoop
| End Sub
|
| ------------------------------------------
|
| Sub RunPause()
| PauseTime = 0.75 ' Set duration.
| Start = Timer ' Set start time.
| Do While Timer < Start + PauseTime
| DoEvents ' Yield to other processes.
| Loop
| End Sub
|
| ------------------------------------------
|
| Private Sub UserForm_Terminate()
| End
| End Sub
 
C

cdb

Jim,

If I make it modeless will it run the rest of the code, or will it keep
looping through the code to make the label flash?

I think the problem I'm having is trying to run two lots of code at once
(one to update in the background which opens up other sheets and imports
data, and one to load up the user form and make the label flash.

Is there any other way of making the text flash without looping through some
code to make the lebel visible and hiding it??
 
J

Jim Rech

I think the problem I'm having is trying to run two lots of code at once

Yes, you must bring them together probably in the Activate event.

--
Jim Rech
Excel MVP
| Jim,
|
| If I make it modeless will it run the rest of the code, or will it keep
| looping through the code to make the label flash?
|
| I think the problem I'm having is trying to run two lots of code at once
| (one to update in the background which opens up other sheets and imports
| data, and one to load up the user form and make the label flash.
|
| Is there any other way of making the text flash without looping through
some
| code to make the lebel visible and hiding it??
|
| "Jim Rech" wrote:
|
| > Unless you make the form's ShowModel property false this is true:
| >
| > UserForm1.Show
| > MsgBox "You won't see me until the form is unloaded/hidden"
| >
| >
| > --
| > Jim Rech
| > Excel MVP
| > | > |I have a workbook with an update button on (which has a macro behind
it).
| > | When I press this button I want a userform to open and display a
flashing
| > | message for the duration of the macro. I have tested all the bits
| > seperately
| > | and they all work fine - the update macro by itself works and updates
the
| > | data and the userform opens and display a flashing message if opened
by
| > | itself.
| > |
| > | The problem I have is, if I put the userform into the update macro,
once I
| > | click the button the userform displays, but none of the code in the
macro
| > | works. I have tried adding the DoEvents code in but this doesn't seem
to
| > help.
| > |
| > | Can anyone help me?
| > |
| > | Code below:
| >
| -------------------------------------------------------------------------------------------------
| > | Sub UpdateData()
| > | DoEvents
| > | UserForm1.Show
| > | ~ My Code
| > | UserForm1.Hide
| > | End Sub
| > |
| > | ------------------------------------------
| > |
| > | Private Sub UserForm_Activate()
| > | Label1.Visible = False
| > | StartLoop:
| > | If Label1.Visible = False Then Label1.Visible = True Else
Label1.Visible =
| > | False
| > | RunPause
| > | GoTo StartLoop
| > | End Sub
| > |
| > | ------------------------------------------
| > |
| > | Sub RunPause()
| > | PauseTime = 0.75 ' Set duration.
| > | Start = Timer ' Set start time.
| > | Do While Timer < Start + PauseTime
| > | DoEvents ' Yield to other processes.
| > | Loop
| > | End Sub
| > |
| > | ------------------------------------------
| > |
| > | Private Sub UserForm_Terminate()
| > | End
| > | End Sub
| >
| >
| >
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top