Excel: Updating Checkbox Cell Link After Worksheet Re-sort 1

[Jamesm601]

Hi all!

I’ve set up an Excel workbook with multiple checkboxes, all in the same column, which control chart creation/deletion and row hiding/unhiding. These checkboxes are from the Forms toolbar. (The kind that has a .linkedcell property) What I need to accomplish is to be able to sort the worksheet, and have the checkboxes not only physically move with the rows in which they are placed, but have the cell reference in their .linkedcell property updated as well.

When I sort the worksheet, Excel does a somewhat effective job of moving the controls with the rows, but it’s not precise. But not surprisingly, the .linkedcell property doesn’t change at all. I guess I’m just looking for some way to reference in VBA, what row the data that was in the original liked row went to after the sort, and update the .linkedcell property accordingly. Either that, or a way for VBA to know, at any point, what row the form control is sitting in. Anyone ever run into this? Any help would be greatly appreciated.

Thanks in advance!!

 

[PHV]

Have you tried to play with the ControlFormat.LinkedCell, the TopLeftCell.Row and/or the BottomRightCell.Row properties of the control ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Jamesm601]

PH,
Thank you SO MUCH!! That was exactly what I needed. All I have to do is sort the worksheet, then...

ActiveSheet.CheckBoxes(a).Linkedcell = "B" & ActiveSheet.CheckBoxes(a).TopLeftCell.row - 1

...to reassign the cell to the left of the object. Now if I can just get Excel to position the objects correctly after the sort (they tend to drift a little), I should be good to go.

Again, thanks a million.

James