Retain value of userform option button

[Gig]

I have a simple user form with three option buttons. I would like the
value(true or false) of the option buttons to remain the same before
the userform is unloaded. I thought I would store the value in a cell
on the spreadsheet with the following code:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As
Integer)
Sheets("sheet1").Range("f1") = OptionButton1.Value
End Sub

But the ob value is always false when the userform is reloaded, even
though it was true when unloaded. Does anyone have a way to do this?

Thanks in advance for any advice!

Greg

 

[Test]

Have you got the command OptionButton1.Value = Sheets("sheet1").Range("f1")
in the UserForm_Open procedure?

 

[john]

Place this code behind your form - it should do what you are looking for:

Private Sub UserForm_Initialize()
Dim ctl As Control
Dim CtrlType As String
'change Userform1 to name of your form
For Each ctl In UserForm1.Controls

CtrlType = TypeName(ctl)

If CtrlType = "OptionButton" Then

ctl.Value = GetSetting(AppName:="MyApp", _
section:="UserForm Settings", _
key:=ctl.Name, Default:=False)

End If
Next ctl
End Sub


Private Sub UserForm_Terminate()
Dim ctl As Control
Dim CtrlType As String
'change Userform1 to name of your form
For Each ctl In UserForm1.Controls

CtrlType = TypeName(ctl)

If CtrlType = "OptionButton" Then

SaveSetting AppName:="MyApp", _
section:="UserForm Settings", _
key:=ctl.Name, setting:=CStr(ctl.Value)

End If
Next ctl
End Sub

 

[Mike Middleton]

Greg -

In addition to storing the value somewhere, you also need to use the user
form Initialize event to use that value (if it has been defined) to set the
option buttons before you Show the user form.

And, you need to use Show and Unload to trigger Initialize and QueryClose,
not Hide (which won't trigger the QueryClose event).

By the way, I typically use a hidden defined name (instead of a worksheet
cell) to store this kind of data.

- Mike

http://www.MikeMiddleton.com