Sorting Checkboxes with Linked Cells

focuswiz

New Member
Joined
Aug 12, 2014
Messages
16
I saw some guidelines in this thread and followed them to the best of my ability:

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.

Capture.PNG
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
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

New Member
Joined
Aug 12, 2014
Messages
16
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,928
Messages
5,621,654
Members
415,849
Latest member
PhoenixRising2015

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top