Event Management

[Nigel]

Hi All

In a useform intialize event I set an OptionButton depending on the values
found in worksheet cells. On the same userform I have set up a click event
for the same OptionButton that if clicked Shows second userform.

If the OptionButton.value is set to 1 in the initialise event of the first
userform the second userfrom loads first and has to be cancelled before the
first userform opens. Clearly the OptionButton_Click event is being
triggered BEFORE the first form loads. I have tried to disable Application
events whilst the first userform is initialising without success.

Any ideas - how I can force the first useform to load, stop the OptionButton
click event from opening the second userform unless the OptionButton is
specifically clicked?

Sample code behind first userform.....

Private Sub UserForm_Initialize()
' load sheet value into controls
If ActiveCell(1, 2).Value = "Yes" Then
OptionButton1.Value = 1 ' the setting of this control triggers the
click event below!
End If
End Sub

Private Sub OptionButton1_Click()
UserForm2.Show
End Sub

 

[Bob Phillips]

Nigel,

Unfortunately, with forms, you have to do it all yourself.

With these type of click events, I add a Boolean, like so

Private fReEntry As Boolean


Private Sub OptionButton1_Click()
If Not fReEntry Then
fReEntry = True
UserForm2.Show
fReEntry = False
End If
End Sub

That is the general technique. It may be a bit overkill here, you can get
away with

Private Sub OptionButton1_Click()
If Not fReEntry Then
UserForm2.Show
End If
End Sub

but the first stops the control event re-firng itself in other conditions.

Then amend the Initialise like so

Private Sub UserForm_Initialize()
' load sheet value into controls
fReEntry = True
If ActiveCell(1, 2).Value = "Yes" Then
OptionButton1.Value = 1
End If
fReEntry = False
End Sub



--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Nigel]

Bob,
I just knew I would need to add some extra control logic you have pointed
the way. Many Thanks