Linking Checkboxes to a cell

[Stubby]

Hello Fellow Excel users.

I have the following code that adds checkboxes to various cells in my spreadsheet:

Set chbox2 = Sheet9.CheckBoxes.Add(.Left, .Top, 200, .Height)
chbox2.Caption = "Click to copy to " & Range("plantype")
chbox2.LinkedCell = .Offset(0, 2).Address(External:=True)
.Offset(0, 2).Font.ColorIndex = 2
chbox2.Name = "Plan" & curformrow
chbox2.OnAction = "LPlan

Al was going well until I sorted my data! To my horror the checkboxes didn't move with the sort.

Therefore my question is: Can you link a checkbox to a cell so that it will move when the data is sorted.?

 

[AlphaFrog]

You can have the checkboxes sort with the data. Three things to check and do.

1. On the Excel Options menu, (Excel 2003: Tools\ Options| Edit Tab), make sure the option Cut, copy, and sort objects with cells is checked.
2. Make sure each checkbox is completely contained within one row only. Each checkbox cannot overlap into any adjacent rows. Looking at your code, it looks like that is the case but double check it.
3. When you sort, include in the selection all columns that the checkboxes overlap e.g.; if your checboxes overlap columns B and C, those columns should be included in the range that you sort. If the checkboxes overlapped column D and you didn't include column D in the sort range, the checkboxes would not follow the sort.

 

[Stubby]

Thanks AlphaFrog. I didn't realise Excel could be so Picky!!

 

[Stubby]

I've hit another snag!

The checkboxes now move when I sort (including the linked cells that return TRUE) however the "checked" status of the checkbox itself does NOT travel with the checkbox.For example: Before a sort My checkbox is in cell T12, it is checked and the linked cell in U12 states TRUE. After a sort the checkbox has moved (I know this as I have altered the caption) to T10, the linked cell U10 still says TRUE however the checked status of the checkbox is still in T12, with the one in T10 unchecked!

Any Ideas !!

 

[Stubby]

Managed to answer my own question!!!!