Option button selection disaapear.

[edlim85]

hi gurus,

I have this option button selection disappearing issue here. I hope to get some help here. I'll try my best to explain.

I have a macro that copy and rename a worksheet template (hidden) everytime the user click on a "+" button. the template contains option buttons for user to make select. the option buttions is named "OptionButton1".... "OptionButton4"..

I also notice this issue only happens when there are more than 1 copied template. I suspect is the way i code the macro. but then again I already declare the worksheet the macro should be working on.

For Each obj In ws.OLEObjects
        If TypeName(obj.Object) = "OptionButton" Then
            If obj.Name <> "OptionButton3" Then
                ws.OLEObjects(obj.Name).Object = False
            End If
        End If
    Next obj

I'm stuck and unsure where is wrong.

 

[Andrew Poulsom]

What do you mean by disappear? What code are you using to copy the worksheet?

 

[edlim85]

Thanks Andrew for your reply

the selection made by user on the option buttons was automatically clear.

i use the follow code to duplicate the template and subsequently rename it

Worksheets("EventTemplate").Copy after:=ws
Worksheets("EventTemplate (2)").Name = wsName

 

[Andrew Poulsom]

What version of Excel are you using and how are you assigning the copied sheet to the ws variable? If OptionButton3 is checked it won't be unchecked. Is that what you want?

 

[edlim85]

Hi Andrew,

The copied sheets are named uniquely. i use activesheet as ws so that the codes only run on activesheet.

im using excel 2010. is it possible to send you the file im working on so what i say is clearer?

 

[Andrew Poulsom]

You can put the workbook on a share like Box.com if you want and post a link to it.

 

[edlim85]

hi Andrew,

I figure that i didnt group my option buttons that could be causing the problem.

is it possible to apply the loop below to a specific option button group?

For Each obj In ws.OLEObjects
        If TypeName(obj.Object) = "OptionButton" Then
            If obj.Name <> "OptionButton3" Then
                ws.OLEObjects(obj.Name).Object = False
            End If
        End If
    Next obj

 

[edlim85]

Thanks Andrew,

I found the solution. the problem surface becos i didnt group my option buttons.

    For Each obj In ws.OLEObjects
        If TypeName(obj.Object) = "OptionButton" Then
            If obj.Object.GroupName = ws.Name Then
                If obj.Name <> "OptionButton1" Then
                    ws.OLEObjects(obj.Name).Object = False
                End If
            End If
        End If
    Next obj