Auto Grouping of Option Buttons
 

I have a worksheet that has a dozen option button groups. This sheet is to be
copied/pasted in the same excel file more than 100 times. Is there a way to
have the option button groups change there groupname automatically, so that I
dont end up doing it manually 1100 times.

Thanks,
Rafat Inayat Elahi

Auto Grouping of Option Buttons
 

Perhaps by using a macro to loop through your controls and manipulate the
required properties.

--
Regards,
Tom Ogilvy


"Rafat" wrote:

I have a worksheet that has a dozen option button groups. This sheet is to be
copied/pasted in the same excel file more than 100 times. Is there a way to
have the option button groups change there groupname automatically, so that I
dont end up doing it manually 1100 times.

Thanks,
Rafat Inayat Elahi

Auto Grouping of Option Buttons
 

Is it possible if you could provide a link or some insight as to how to
accomplish that?

Thanks,
Rafat I Elahi



"Tom Ogilvy" wrote:

Perhaps by using a macro to loop through your controls and manipulate the
required properties.

--
Regards,
Tom Ogilvy


"Rafat" wrote:

I have a worksheet that has a dozen option button groups. This sheet is to be
copied/pasted in the same excel file more than 100 times. Is there a way to
have the option button groups change there groupname automatically, so that I
dont end up doing it manually 1100 times.

Thanks,
Rafat Inayat Elahi

Auto Grouping of Option Buttons
 

Sub ListData()
Dim obj As OLEObject
Dim sh As Worksheet
Dim ob As MSforms.OptionButton
Dim sh1 As Worksheet
Set sh1 = Worksheets.Add(after:=Worksheets(Worksheets.Count) )
rw = 2
sh1.Cells(1, 1) = "Sheet"
sh1.Cells(1, 2) = "Object"
sh1.Cells(1, 3) = "GroupName"
For Each sh In Worksheets

If sh.Name < sh1.Name Then
For Each obj In sh.OLEObjects
If TypeOf obj.Object Is MSforms.OptionButton Then
Set ob = obj.Object
sh1.Cells(rw, 1).Value = sh.Name
sh1.Cells(rw, 2).Value = ob.Name
sh1.Cells(rw, 3).Value = ob.GroupName
rw = rw + 1
End If
Next
End If
Next
End Sub

this gives you a sample of the structure of code you would need. It just
lists all the option buttons and their group names - it should be a short
step from there to assign new group names to them.

--
Regards,
Tom Ogilvy


"Rafat" wrote:

Is it possible if you could provide a link or some insight as to how to
accomplish that?

Thanks,
Rafat I Elahi



"Tom Ogilvy" wrote:

Perhaps by using a macro to loop through your controls and manipulate the
required properties.

--
Regards,
Tom Ogilvy


"Rafat" wrote:

I have a worksheet that has a dozen option button groups. This sheet is to be
copied/pasted in the same excel file more than 100 times. Is there a way to
have the option button groups change there groupname automatically, so that I
dont end up doing it manually 1100 times.

Thanks,
Rafat Inayat Elahi

Auto Grouping of Option Buttons
 

Thank you very much. I really appreciate your effort.
Rafat

"Tom Ogilvy" wrote:

Sub ListData()
Dim obj As OLEObject
Dim sh As Worksheet
Dim ob As MSforms.OptionButton
Dim sh1 As Worksheet
Set sh1 = Worksheets.Add(after:=Worksheets(Worksheets.Count) )
rw = 2
sh1.Cells(1, 1) = "Sheet"
sh1.Cells(1, 2) = "Object"
sh1.Cells(1, 3) = "GroupName"
For Each sh In Worksheets

If sh.Name < sh1.Name Then
For Each obj In sh.OLEObjects
If TypeOf obj.Object Is MSforms.OptionButton Then
Set ob = obj.Object
sh1.Cells(rw, 1).Value = sh.Name
sh1.Cells(rw, 2).Value = ob.Name
sh1.Cells(rw, 3).Value = ob.GroupName
rw = rw + 1
End If
Next
End If
Next
End Sub

this gives you a sample of the structure of code you would need. It just
lists all the option buttons and their group names - it should be a short
step from there to assign new group names to them.

--
Regards,
Tom Ogilvy


"Rafat" wrote:

Is it possible if you could provide a link or some insight as to how to
accomplish that?

Thanks,
Rafat I Elahi



"Tom Ogilvy" wrote:

Perhaps by using a macro to loop through your controls and manipulate the
required properties.

--
Regards,
Tom Ogilvy


"Rafat" wrote:

I have a worksheet that has a dozen option button groups. This sheet is to be
copied/pasted in the same excel file more than 100 times. Is there a way to
have the option button groups change there groupname automatically, so that I
dont end up doing it manually 1100 times.

Thanks,
Rafat Inayat Elahi