Problem with Option button control in multiple sheets

[master2]

My application uses a worksheet which contains some action buttons and
an OptionButton ActiveX control. All the code for the buttons is in
modules and the option button has no code at all - I just need to be
able to read its value. The idea is that the user creates a new
instance of this data-entry "form" by creating a copy of the worksheet.
The buttons get copied along and continue to function independently
across worksheets, using the same code. The option button, however,
does not retain its value on each sheet. When I change it on one sheet,
it loses its value in all others! What can I do to fix this? Note that
I access the button by prefixing it with the worksheet, e.g.
ActiveSheet.OptionButton1.Value, but if I click on it in one sheet,
then all its instances in all other sheets turn to False.

Thanks!

 

[Dave Peterson]

I think it's the groupname of the optionbuttons that causing the trouble.

When I changed the groupname, the optionbuttons were independent of the previous
sheet.

If you provided a macro to the user to copy the worksheet, you could change the
groupname at that time.

Or maybe you could just use the optionbuttons from the Forms toolbar????

 

[master2]

Dave,
Thanks, that works great! I didn't want to force a custom "copy"
procedure on the user, but the optionbuttons from the forms toolbar
seem to work just fine! You simply need to access them a little
differently, as
ActiveSheet.Shapes("buttonName").ControlForrmat.Value=xlOn|xlOff and
the problem is solved.

Thanks again!

 

[Dave Peterson]

Or even directly through the OptionButtons collection:

ActiveSheet.OptionButtons("OptionButtonName").Value=xlOn 'xlOff