Reset all radio button to blank when it is linked to a cell using macro in excel

[Dee]

Dear Experts,

I have a macro which reset all the radio button to blank at a click but the same will not work when the radio button are linked to a cell.

Here is my codes...


Private Sub Worksheet_Activate()

For x = 1 To Sheets("B.").OLEObjects.Count

Sheets("B.").OLEObjects(x).Object.Value = False

Next x


End Sub


Thanking you in advance,

Dee

 

[NARAYANK991]

Hi Dee ,


I am not able to reproduce your problem ; when I execute your macro , all the buttons get cleared , whether they are linked to cells or not.


Narayan

 

[Dee]

if i add the radio button by Form Controls and link it to a cell it will not work,if i add radio buttons by ActiveX control it works but i cant link it a cell.


Dee

 

[NARAYANK991]

Hi Dee ,


I am still not able to understand your problem.


Suppose you add option buttons ( say 3 of them ) to your worksheet ; you link each of them to a different cell ; now you run your macro ; what happens ?


You say when you add the Forms control option button and link it to a cell , it does not work ; what does not work ?


What do you actually wish to do ?


Narayan

 

[Dee]

Narayan, Thanks for your patience...


I have 3 radio button in a sheet all are linked to Cell A3. First time i am selecting one radio button save my sheet. Next time when i reopen i need to set all my radio button to blank at a click. For this i am using the above said code. How do i do that as my above code is not impressive...

i go tone more code as below...


Sub reset()

Dim OptBTN As OptionButton

Dim OLEObj As OLEObject

For Each OptBTN In ActiveSheet.OptionButtons

OptBTN.Value = xlOff

Next OptBTN

For Each OLEObj In ActiveSheet.OLEObjects

If TypeOf OLEObj.Object Is msforms.OptionButton Then

OLEObj.Object.Value = False

End If

Next OLEObj

End Sub


Thanks a lot

Dee

 

[NARAYANK991]

Hi Dee ,


The following code seems to work on my PC :

[pre]

Sub reset()
Dim OptBTN As OptionButton
Dim OLEObj As OLEObject
For Each OptBTN In ActiveSheet.OptionButtons
OptBTN.Value = xlOff
Next OptBTN
End Sub

[/pre]

Narayan

 

[Dee]

hey!!

Finally ended up with this code


Sub ClearOptionButtonsForms()

Sheets("B.").Select


ActiveSheet.OptionButtons.Value = xlOff


End Sub

 

[NARAYANK991]

Hi Dee ,


Thanks for the feedback.


Narayan