How to save the option button value

[sam]

Hi,

I have a worksheet, depending on different choice in a dropdown list,
corresponding range which including a couple of option buttons should
be filled.


Here is an code sample:

If Worksheets("Sheet1").Range("E5").Value = "1" Then
Set rng = Worksheets("Sheet1").Range("E8:H10")
' Set all the value of option buttons in this range to false
' and disable all the option buttons
For Each obj In Worksheets("Sheet1").OLEObjects
If TypeOf obj.Object Is MSForms.OptionButton Then
If Not Intersect(obj.TopLeftCell, rng) Is Nothing Then
obj.Object.Value = False
obj.Object.Enabled = False
End If
End If
Next obj

Set rng1 = Worksheets("Sheet1").Range("E8:F10")
' enable the option buttons in the range(E8:F10)
For Each obj In Worksheets("Sheet1").OLEObjects
If TypeOf obj.Object Is MSForms.OptionButton Then
If Not Intersect(obj.TopLeftCell, rng1) Is Nothing Then
obj.Object.Enabled = True
End If
End If
Next obj

Else

If Worksheets("Sheet1").Range("E5").Value = "2" Then
Set rng = Worksheets("Sheet1").Range("E8:H10")
' Set all the value of option buttons in this range to false
' and disable all the option buttons
For Each obj In Worksheets("Sheet1").OLEObjects
If TypeOf obj.Object Is MSForms.OptionButton Then
If Not Intersect(obj.TopLeftCell, rng) Is Nothing Then
obj.Object.Value = False
obj.Object.Enabled = False
End If
End If
Next obj

Set rng1 = Worksheets("Sheet1").Range("G8:H10")
' enable the option buttons in the range(G8:H10)
For Each obj In Worksheets("Sheet1").OLEObjects
If TypeOf obj.Object Is MSForms.OptionButton Then
If Not Intersect(obj.TopLeftCell, rng1) Is Nothing Then
obj.Object.Enabled = True
End If
End If
Next obj

End if
End if

The basic idea of this code is: first, set the value of each option
button in the worksheet to false and disable all of them; then, based
on different choice in dropdown list, option buttons in specific range
enable.

After I ticked option buttons in specific range, I saved this
worksheet. However, I encountered one problem when I open this saved
worksheet. All the value of option buttons in the specific range
changed to false, no matter what I chose before save. I want to get the
worksheet with option button value saved.

Do you have some ideas to fix this problem? Thank you in advance!!!


Best Regards

Sam

 

[Bob Phillips]

Link the buttons to cells, separate cells, then it will pick up those values
next time.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[sam]

Hi,

Could you explain more clearly? Thank you!

The option buttons I used are from control toolbox. The option button
are groups of Yes or No choice. I just want to record the yes or no
info. If I choose yes, the value of that specific option button will be
ture.

You mean I can link the buttons to some cell. You mean using cells to
record related info?

Best Regards

Sam

 

[Bob Phillips]

If you go into design mode, the blue-green triangle on the controls toolbox
toolbar, then double click the option button. This will take you to the
VBIDE for that object. If the properties box is not displayed, show it with
F4. In the properties for the optionbutton, you will see a LinkedCell
property. Link each button in this way to a separate cell, preferably a cell
off-screen or in a hidden column.

When either button is selected, that linked cell will get a value TRUE,
others will get a value of FALSE (unless you have groups, in which case the
related items will change). Saving the workbook saves these values, so
opening it will re-set the option buttons.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[sam]

Hi

I think we misunderstand each other, we talked at cross-purposes here


I got your ideas. Anyway,thank you for this idea. However, My idea is
to make the option button choice visible to users directly. It is not
to record the info in some other cells. I want to find some approach
try to avoid the value of option button to reset when I open the saved
option button choice again. Do you have some suggestions? I am
wondering whether it is possible to add some code to prevent the value
of option button to reset.

Best Regards

Shang

 

[Duncan]

Sam,

If you are closing the userform and then displaying again it will not
have saved the previous values in order to "reset" them. It only holds
these in memory while the form is displayed.

What you would need to do as suggested is to store the 'previous' value
in a cell (on hidden sheet would be best) so that when you reload you
can restore the previous values.

Duncan

 

[Bob Phillips]

That is what I gave you. Try it and see.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[sam]

I see this is possible solution. I appreciate both of your help!