Formula in "Cell Link" box of Option Button?

[Zzed]

I have a worksheet with several hundred option buttons, grouped into
groups of 3. I want the "linked cell" for each group of three to be in
cell "Bx", where "x" is the row number that the option button group
exists within. For example, the option button group in row 4 would
have their linked cell be "B4".

The "Cell Link" box for the option buttons doesn't allow a formula
(like =ADDRESS(2,ROW(),4) for example). I tried defining a name for a
formula and then using the name in the "Cell Link" box, and while I'm
allowed to do that, the option buttons become completely
"un-click-able".

Is there a way to automate the "Cell Link" value? I'd really rather
not have to manually enter the proper linked cell address for each
option button group.

-=Zzed=-

 

[Dave Peterson]

I think the answer you get will depend on what type of optionbuttons you used.
You could have used optionbuttons from the Forms toolbar or optionbuttons from
the Control Toolbox toolbar.

And each of these behaves differently.

I find that the controls from the Forms toolbar behave nicer on worksheets.

If you used these, then each group of 3 optionbuttons has to be in its own frame
(an icon on that same Forms toolbar).

If you want, here is some code that adds the option buttons, frames, and assigns
a linked cell to the group.

Option Explicit
Sub testme01()

Dim grpBox As GroupBox
Dim optBtn As OptionButton
Dim maxBtns As Long
Dim myCell As Range
Dim myRange As Range
Dim wks As Worksheet
Dim iCtr As Long

maxBtns = 3

Set wks = ActiveSheet
With wks
Set myRange = .Range("c4:c9")
myRange.EntireRow.RowHeight = 28
myRange.Resize(, maxBtns).EntireColumn.ColumnWidth = 4
.GroupBoxes.Delete
.OptionButtons.Delete
End With
For Each myCell In myRange
With myCell.Resize(1, maxBtns)
Set grpBox = wks.GroupBoxes.Add _
(Top:=.Top, Left:=.Left, Height:=.Height, _
Width:=.Width)
With grpBox
.Caption = ""
.Visible = True 'False
End With
End With
For iCtr = 0 To maxBtns - 1
With myCell.Offset(0, iCtr)
Set optBtn = wks.OptionButtons.Add _
(Top:=.Top, Left:=.Left, Height:=.Height, _
Width:=.Width)
optBtn.Caption = ""
If iCtr = 0 Then
With myCell.Offset(0, -1)
optBtn.LinkedCell = .Address(external:=True)
'.NumberFormat = ";;;"
End With
End If
End With
Next iCtr
Next myCell

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

I'd copy your worksheet (just in case), remove the existing optionbuttons and
modify the macro (I used c4:c9 to locate the first optionbutton of each group)
and test it out.

 

[Zzed]

Worked like a charm!! I altered it a little, just to fit the buttons
into my existing column and row sizes, but otherwise, it works exactly
like I'd hoped! Thanks again, Dave!

-=Zzed=-