Suppose I want to use a form which prompts the user to
select a cell and press an OK buttom on the form when
ready. I figure the form must be modeless, but how do I
prevent the code following the statement
frmMyForm.Show
from executing until the OK button on the form has been
clicked?

TIA, Arne

One way to select a cell without having to reinvent the
wheel, via a modless form, is to use:

Dim myRange as Range
Set myRange = Application.InputBox( _
"Select a cell", Type:=8)


But if your form is used to do more than just pick a cell,
then to prevent code from executing until the OK button is
clicked, you can use a public boolean flag and a DoEvent
within a Do While loop.

In your module's declarations section at the top:
Public OKButtonClicked as Boolean

In the VBA routine that calls the form:

Private MySubroutine()
'some code
OKButtonClicked = False
frmMyForm.Show
Do While Not OKButtonClicked
DoEvents
Loop
'some more code
End Sub

And finally in your form's OK button:
Private Sub cmdOK_Click()
'Your Code
OKButtonClicked = True
End Sub


Or as an alternate to the boolean flag, I often hide the
form after the OK button is clicked, so I might use:

frmMyForm.Show
Do While frmMyForm.Visible
DoEvents
Loop
'More code here


-----Original Message-----
Suppose I want to use a form which prompts the user to
select a cell and press an OK buttom on the form when
ready. I figure the form must be modeless, but how do I
prevent the code following the statement
frmMyForm.Show
from executing until the OK button on the form has been
clicked?

TIA, Arne
.

Great, thanks for the information.

Arne

Do you really need the flag to check, if OK-button is
pressed.

If you create an user form and set the ShowModal property
False (if you don't have the Properties window, select it
from View menu), then you get this wanted functionality.
After that you just create OK-button and put Unload Me in
the end of the button's code.

In this case you can call the user form normally
(UserFrm.Show) and the code won't be executed until OK-
button is pressed.

Sub Test

UserFrm.Show 'waits until OK-button is pressed
Code continues...

End Sub

Or am I missing something here...

Ecco

-----Original Message-----
One way to select a cell without having to reinvent the
wheel, via a modless form, is to use:

Dim myRange as Range
Set myRange = Application.InputBox( _
"Select a cell", Type:=8)


But if your form is used to do more than just pick a
cell,
then to prevent code from executing until the OK button
is
clicked, you can use a public boolean flag and a DoEvent
within a Do While loop.

In your module's declarations section at the top:
Public OKButtonClicked as Boolean

In the VBA routine that calls the form:

Private MySubroutine()
'some code
OKButtonClicked = False
frmMyForm.Show
Do While Not OKButtonClicked
DoEvents
Loop
'some more code
End Sub

And finally in your form's OK button:
Private Sub cmdOK_Click()
'Your Code
OKButtonClicked = True
End Sub


Or as an alternate to the boolean flag, I often hide the
form after the OK button is clicked, so I might use:

frmMyForm.Show
Do While frmMyForm.Visible
DoEvents
Loop
'More code here


-----Original Message-----
Suppose I want to use a form which prompts the user to
select a cell and press an OK buttom on the form when
ready. I figure the form must be modeless, but how do I
prevent the code following the statement
frmMyForm.Show
from executing until the OK button on the form has been
clicked?

TIA, Arne
.

.

That does not work in Excel 2000. The code following the
show method call is executed right away.

You're right. My memory didn't serve me right this time.
Thank you for your correction.

Ecco

-----Original Message-----
That does not work in Excel 2000. The code following the
show method call is executed right away.

.

Ah, more useful advice.
Thanks a bundle.