Preserve last selection of an OptionButton

[Edmund]

How can I get VBA to remember the last used selection for a group of Option
Buttons in UserForm1?

I have these buttons in a group in UserForm1:
OptionButton1
OptionButton2
OptionButton3
OptionButton4
OptionButton5

If the user selects OptionButton3 on his last visit, I would like this
button to remain checked the next time he displays UserForm1.
Say another week later, if he selects OptionButton5, I'd like the UserForm
to remember this selection being the last used selection.
This is exactly how the buttons in Tools>Option appears.

Thanks a lot.

 

[Gary Keramidas]

here's what i use

i have an checkbox on a menu, when it's checked, it loads on start up, when it's
not, the user has to click the menu button

so when the menu button is checked, it wrtie "true" to u2 on the work sheet. i
also chenge the label text

Private Sub CheckBox2_Click()
If Me.CheckBox2 = True Then
Worksheets("work").Range("u2").Value = True
With Me.Label2
..Caption = "Click to disable auto load on startup."
..Height = 20
..Width = 80
..Left = 75
End With
Else
Worksheets("work").Range("u2").Value = False
With Me.Label2
..Caption = "Click to enable auto load on startup."
..Height = 20
..Width = 80
..Left = 75
End With
End If
end sub

then on userform initialize

If Worksheets("work").Range("u2").Value = True Then
Me.CheckBox2 = True

With Me.Label2
..Caption = "Click to disable Menu load on startup."
..Height = 20
..Width = 80
..Left = 75
End With
GoTo Fin:
Else
Me.CheckBox2 = False

With Me.Label2
..Caption = "Click to load Menu on startup."
..Height = 20
..Width = 80
..Left = 75
End With
End If
Fin:
End Sub

 

[NickHK]

Edmund,
You have to save the value somewhere and as you are woking in Excel, then
the natural place would be a value in a worksheeet.
You can hide the sheet, so the user cannot interfer.

Then when you initialise you form next, read the value and set the Option
button.

NickHK

 

[halimnurikhwan]

Hi All,
May be NickHK means:

try this ... :

Private Sub UserForm_Initialize()
For Each ctl In Me.Controls
If TypeOf ctl Is MSForms.OptionButton Then
If InStr(1, Range("a1").Value, ctl.Name) > 0 Then
ctl.Value = True
ctl.SetFocus
Exit For
End If
End If
Next ctl
End Sub

Private Sub UserForm_Terminate()
For Each ctl In Me.Controls
If TypeOf ctl Is MSForms.OptionButton Then
If ctl.Value Then
Range("a1").Value = ctl.Value & " " & ctl.Name
Exit For
End If
End If
Next ctl
End Sub



Regards,

Halim



NickHK menuliskan:

 

[halimnurikhwan]

Hi Edmund,

You can also using SaveSetting and GetSetting function ..

Note that SaveSetting is saving value into Registry and
GetSetting is retrieve value from Registry

Rgds,

Halim


Edmund menuliskan: