Make option button link permanent to cell

[dgold82]

With the help of Trevor Williams on this board I was able to easily link
hundred of group boxes (with 4 radio buttons in each) to the cell that they
were on top of. Here is the code:

Sub LinkOptBtns()
Dim GrpBox As GroupBox
Dim OptBtn As OptionButton
For Each OptBtn In ActiveSheet.OptionButtons
With OptBtn
.LinkedCell = .GroupBox.TopLeftCell.Address
End With
Next
End Sub


My problem is that I am starting to lose the links and I am finding myself
running the code randomly to re-link them. Can someone help me make the
control links permanent? I want to distribute my workbook to many users and
will not allow them to access to the VBA.

Thanks.

 

[jamescox]

I'm not sure (and I'm not able to test this just now) but the proble
may be that the linked cell is being assigned as an absolute referenc
and any row or column insertions or deletions you are making may b
causing problems.

To test this, you can try making the following change:

.LinkedCell = .GroupBox.TopLeftCell.Address(0,0)

This change will give instead of an absolute reference ($R$4)
relative one (R4) - which might work better - or maybe not

Good luck with this

 

[dgold82]

Thanks, James. I tried adding a row here and there within the worksheet and
it didn't appear to break the links. It seems to be happening randomly.
Perhaps you are partially right--I might be making changes somewhere that is
affecting the linked cells. I'm thinking that I should run the linking macro
last when I know I am not making any more changes to the workbook.

Wish I had a better way to test this...those radio buttons are my main
source of input for the workbook. Appreciate the help!