Please help if you can. In my Excel macro, Sheet 1 determines if value 1 is greater than value 2 and if so, calls UserForm1. UserForm1 is a simple form, with some text and an OK button. Clicking the OK button closes the UserForm. So far, so good. When UserForm1 opens up, I need it to stay on the screen until a user clicks the OK button. While it is up, I need the rest of the macro to function. If I highlight UserForm1 and view code, I get my code:

Private Sub UserForm1_Activate()
MsgBox "This is a test - hello"
'Place code here that or call another sub rotine from here
End Sub

Problem is, this refuses to show me the message box. What I am doing wrong? Why won't the UserForm1_Activate() work?

Doug

Doug,

DoEvents is your keyword here. Keep in mind that it's not really running in
parallel.

Private blnExitSignal As Boolean
Private i As Long

Private Sub UserForm_Activate()
blnExitSignal = False
i = 0
Do Until blnExitSignal
i = i + 1
DoEvents
Loop
End Sub

Private Sub CommandButton1_Click()
blnExitSignal = True
MsgBox i
Me.Hide
End Sub


Rob


"Doug" wrote in message
...
Please help if you can. In my Excel macro, Sheet 1 determines if value 1
is greater than value 2 and if so, calls UserForm1. UserForm1 is a simple
form, with some text and an OK button. Clicking the OK button closes the
UserForm. So far, so good. When UserForm1 opens up, I need it to stay on the
screen until a user clicks the OK button. While it is up, I need the rest of
the macro to function. If I highlight UserForm1 and view code, I get my
code:

Private Sub UserForm1_Activate()
MsgBox "This is a test - hello"
'Place code here that or call another sub rotine from here
End Sub

Problem is, this refuses to show me the message box. What I am doing
wrong? Why won't the UserForm1_Activate() work?

Doug

Any code I attach to a userform would be what to do if the
userform is exited eg. decrease the no. of invoices,
receipts etc. issued. My experiences have indicated that
processing is suspended untill a response is received from
a user.

if the user clicks OK the processing of coding attached
will occur. if you have used "Application.ScreenUpdating =
False" as a first line in your coding your userform will
stay put untill all the code has been processed. at the
end of the coding you could open the form again
and "Application.Screenupdating = True".

I hope this assists you


-----Original Message-----
Please help if you can. In my Excel macro, Sheet 1
determines if value 1 is greater than value 2 and if so,
calls UserForm1. UserForm1 is a simple form, with some
text and an OK button. Clicking the OK button closes the
UserForm. So far, so good. When UserForm1 opens up, I need
it to stay on the screen until a user clicks the OK
button. While it is up, I need the rest of the macro to
function. If I highlight UserForm1 and view code, I get
my code:

Private Sub UserForm1_Activate()
MsgBox "This is a test - hello"
'Place code here that or call another sub rotine from here
End Sub

Problem is, this refuses to show me the message box. What
I am doing wrong? Why won't the UserForm1_Activate() work?

Doug
.

Not sure what you mean by wanting the form to stay up
while th e rest of the code runs...

I'll take a guess that your procedure is

....code1
userform1.Show
....code2

and you want code2 to run while the form is displayed.
This is a function of the MODAL state. By default, the
userform.Show method is modal
This means that code execution waits for the form to
close before continuing with code2. Force the form to
open in modeless state then code will continue event
while the form is showing

....code1
userform1.Show vbModeless
....code2

Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
Please help if you can. In my Excel macro, Sheet 1
determines if value 1 is greater than value 2 and if so,
calls UserForm1. UserForm1 is a simple form, with some
text and an OK button. Clicking the OK button closes the
UserForm. So far, so good. When UserForm1 opens up, I
need it to stay on the screen until a user clicks the OK
button. While it is up, I need the rest of the macro to
function. If I highlight UserForm1 and view code, I get
my code:

Private Sub UserForm1_Activate()
MsgBox "This is a test - hello"
'Place code here that or call another sub rotine from
here
End Sub

Problem is, this refuses to show me the message box.
What I am doing wrong? Why won't the UserForm1_Activate()
work?

Doug
.