Find & Replace Cell Links for Check Boxes

[Guest]

Hi,

I have a number of surveys where the answers are input by Y/N Check Boxes.
The Check Boxes are linked into a "Stats" spreadsheet with the True & False
answers. I need to update the survey spreadsheets for the new period which
means that the cell links need to be changed to a new column in the "Stats",
but keeping the same row.

Up to now I have been going into each Check Box and changing the links one
at a time, but it is taking forever (I.e. Changing $C$106 to $D$106). Is
there a way to select/group all (or even some) of the Check Boxes and change
the column link on mass???

Thanks in advance
TC

 

[Dave Peterson]

Are these checkboxes from the Forms toolbar?

If yes:

ption Explicit
Sub testme()
Dim CBX As CheckBox
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

For Each CBX In wks.CheckBoxes
CBX.LinkedCell = Application.Range(CBX.LinkedCell).Offset(0, 1) _
.Address(external:=True)
Next CBX
End Sub

 

[Guest]

Best solution is to copy and paste the previous values (paste special -
values) then put them somewhere safe for the time being (say you have 900
values select c2:c901 for example.) into a new clean sheet. Now select all
of the results cells (c2:c901 and drag them over 1 cell to column D, d2:d901
this will automatically change the links.

Now paste the old results back into the position in column c, c2:c901 again

That will work if there isnt a lot of follow on calculations, but I bet
there are...in which case & bear with me this is tricky but it will work

First of all make a copy of the workbook that you started with, all
checkboxes pointing at c

From your [main workbook copy] containing the checkboxes & stats sheets,
select the Sheet with the check boxes and the stats sheet together using CTRL
and MOVE them into a new workbook, save as a file with a [new name]. Close
the original file [main workbook copy] without saving

Now select column C in Stats your new file & insert a new column this will
automatically change the links to d.

Now Copy the check box sheet back into your original file [main workbook
copy] (do not copy the stats page as well).

Looking at the links now they are [new file]'stats'!d6 etc right? Ok now go
to Edit-->links Click the change source button and select the file [main
workbook copy] i.e. the one you are in. Now the checkbox links will be
directed towards the Stats file in [main workbook copy] but will be pointing
at d not c