Which button was pressed?

[Adrian]

I have a custom dialogue that I need to use in two very
different macros. Hence my OKButton_Click routine is empty
except for Unload UserForm, so that the calling macros can
each do their own thing.

But how can the calling macros tell if it was the OKbutton
or the Cancelbutton that was pressed?

 

[Bob Phillips]

--

HTH

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

 

[Bob Phillips]

Adrian,

They would have separate click even ts so you can tell that way.

--

HTH

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

 

[Adrian]

Sorry Bob, I don't understand.

I have written Private subs that make my userform (named
ChooseCohort) work as required.
I've summoned the dialog from a Module (see below) but
after closing the dialog nothing happens (i.e. I can never
get the Msgbox to appear). That's one problem.
I'm calling the userform from a module (instead of putting
the code into the userform) so that I can use the form in
several different subs.

My other problem, and the one that I mentioned in my first
posting, is that I want it to do nothing if the Cancel
button was the way out of the userform.

Sub Test()
ChooseCohort.Show
If ChooseCohort.CheckBoxER1.Value = True Then MsgBox "ER1"
End Sub

 

[Bob Phillips]

Adrian,

I see what you mean now.

If you Unload the form, the form is cleared from memory, so that when you do
the
If ChooseCohort.CheckBoxER1.Value = True Then MsgBox "ER1"
test it re-loads the form, but in it's initial state.

Instead of unloading the form, just hide it, it will saty in memory tehn.

Me.Hide

--

HTH

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

 

[Adrian]

Getting closer... thanks.
Two questions.

1. When (and how) do I unload the userform? Or do I leave
it and include some code to reset all the values before
the next .Show?

2. There is still the problem of distinguishing between
the OK and Cancel buttons. eg. the code below displays the
message whichever of those two I click.

Sub ChooseCohorts()
ChooseCohort.Show
If ChooseCohort.CheckBoxER1.Value = False Then MsgBox "Not
ER1"
End Sub

 

[Bob Phillips]

Adrian,

1. It depends, but the only 'true' answer is when you are absolutely done
with the form. Now it may be that there is no way to ascertain that, and
hence no way to identify where to unload it, but usually there is an
end-point somewhere. The main disadvantage is that memory is held by the
form until you unload it, but unless it is a big form with lots of code, or
there are many forms, this shouldn't be an issue.

2. I still do not understand this question. You talk of OK and Cancel
buttons, but the code refers to a Checkbox. If you want to know which button
was clicked, create a public variable, outside of procedure scope, and set
that in the button click events. Then test like so

If whichButton = "OK" then

where this assumes the variable is created in the standard module. If you
create it in the form module, you will need to qualify with the class

If ChooseCohort.whichButton = "OK" then

--

HTH

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

 

[ross]

Yo,

puting

Me.Hide

in the ok buttons click event will hide the userform, but, and i'm
not sure i full understand what you are try to do, that not so good
for you, becasue you want to do diffrent things based on the choice
made on the userform,

Try this way:

some code to open the user form

YourFormName.Show

Now the form is open. It has all the option you might need on it. The
user chech on box or option and click ok.

now you must tell the programe what to do, based on what the user has
clicked so..

OKbutton_click

if optionbox1.value=true then
NameOfYourSubToGoTOIfThisCondtionIsPicked
elseif
AnotherSub
Else if

etc etc

'''Now close you form.

me.close

end sub


next time you open this form the last check box will still be checked,
but the sub will not run unless the user pressess OK


Good Luck

ROss