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" wrote in message
ups.com...
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