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

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

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

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

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