Sorting Checkboxes with Linked Cells

[focuswiz]

I saw some guidelines in this thread and followed them to the best of my ability:

Is it possible...to sort CHECK BOXES

I'd like to be able to sort check boxes.... is this possible? :o

www.mrexcel.com

My checkboxes definitely move with their cells, but I am having a problem with the linked cell referencing.

I have a large spreadsheet (200 rows of data with 10 columns of checkboxes in each row [among other data]) where I was trying to track status progress using checkboxes (linked to the relevant cell) rather that Yes/No Dropdowns. Using Conditional Formatting made this work quite well and it was easy to update. Then, I needed to add tasks and sort them into place. That is where my problems began.

Is there some form of referencing (like some kind of indirect cell referencing) I can use to identify the linked cell so that it moves with the checkbox? Might I need to invoke a function to relink the cells using VBA whenever the cells are sorted?

In order to ask for assistance here, I created a spreadsheet to demonstrate the problem and attached is a picture of the cells after sorting. Originally, they were ordered by ROYGBIV so that the first row was Red, then Orange, then Yellow, etc. The cells were colored by these colors and the checkbox background was likewise the same color. Thus, if any checkbox did not move with its respective cell, the color of the checkbox fill color would be haloed by a different cell fill color.

After sorting these alphabetically, all of the checkboxes obviously moved with their respective cells (in other words, all of the checkbox fill colors matched the cell fill colors).

However, they remained linked to the cells they had originally been linked to. In other words, checking the Red box, manipulates the first row (which is now blue) and checking the Violet Box will manipulate the last (which is now yellow). In other words, the Linked Cell does not change.

I got the same results with both Form Controls and ActiveX Controls. I got the same results with both absolute and relative addressing in the Linked Cells.

My main concern is to ensure the linked cells are re-aligned when the rows are sorted. Perhaps I could trigger a function to re-assign the linked cell whenever a sort occurs. If that is the solution, is there some kind of event that I can use to trigger the re-assignment when a sort is detected?

Thanks for any help you might offer.

 

[Yongle]

is there some kind of event that I can use to trigger the re-assignment when a sort is detected?

There is not a specified After_Sort event
Simple workaround - if any cell in range being sorted (including header row) includes a volatile formula, then sorting triggers Worksheet_Calculate

To test workaround

1. In above table, replace string Hex in top left cell of your picture with this formula (which displays Hex as before)
=LEFT("Hex"&TODAY(),3)

2. Place this in sheet code window

Private Sub Worksheet_Calculate()
    MsgBox "Detected"
End Sub

3. Sort the range with Data Filter or Data Sort

4. The message should pop up (obviously this detects all calculation events - but that should not matter)

 

[focuswiz]

Thanks, Yongle. That is a neat trick. One thing I was thinking about was to create a field that CONCATs the cells in a column which would most likely change if the data is sorted. There is a rather unique field which would be reliable to do this. Then, I could capture that field changing in Worksheet_Calculate event.

I am beginning to believe that there is no easy way to keep the cell links. Last night I was experimenting with linking each cell to another set of rows and columns and then having a formula associated with each cell to do a lookup for the value in that table. The table of linked values will remain static, but the lookup formulas will move with the checkboxes and continue to retrieve the linked data. The problem with this is hard-coding a lookup for each cell behind each cell with a checkbox. Not really "hard" just a bit tedious (over 2000 cells).

The other part that was tricky was being able to hide rows without the boxes "bleeding into other areas of the worksheet. Apparently, I needed to set the option "Move and size with cells" which Microsoft seems to hide fairly well when using Form Controls. This has been quite a learning experience.

Thanks again.