OptionButtons

[sapka]

Please download attached Excel file. You will find one UserForm there, run it.
Two times select option 2 and choose "No" and close UserForm.

1. Why I am asked "Change it?" just after UserForm loads (no selections are made)?
2. More importantly, why I am two times asked "Change it?" when closing the UserForm?

I do not want any of those events to happen. How to fix it?

File: http://www.2shared.com/file/xRUKUaul/userform.html

 

[sapka]

Anyone?

 

[Jerry Sullivan]

Your current code affecting Option1 is:

Dim DisableEvents As Boolean
 
Private Sub UserForm_Activate()
    DisableEvents = True
    OptionButton1 = True
    DisableEvents = False
End Sub
 
Private Sub OptionButton1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If DisableEvents Then Exit Sub
    If MsgBox("Change it? Yes/No", vbYesNo) = vbYes Then OtherFunction Else Cancel = True
End Sub

The reason that you get the Msgbox before any selections are made is that the UserForm_Activate has already set the DisableEvents=False before OptionButton1_BeforeUpdate event occurs. To tell VBA to wait until all other events are completed before continuing with _Activate, you could add a DoEvents statement...

Private Sub UserForm_Activate()
    DisableEvents = True
    OptionButton1 = True
    DoEvents
    DisableEvents = False
End Sub

The multiple prompts you are getting are due to the use of the _BeforeUpdate event. I'd suggest you change your triggering event from OptionButton1_BeforeUpdate to OptionButton1_Click. This will provide one check for your MsgBox("Change it? Yes/No.... statement.