Using a check box to trigger an autosort (2003 SP2

Topic

New Reply

Hi all

I’ve created a simple to-do list in Excel that re-sorts automatically, triggered by a change in some columns. And there lies my problem…

The columns that do *not* trigger a change contain either drop-down lists or a check box for “Complete” status (and each check box’s corresponding “TRUE” or “FALSE” in the adjoining cell). If I change the cell contents in any of the other columns (i.e. with the keyboard, cut, paste, etc.), the sort is triggered perfectly. But making a drop-down box selection or changing a check box won’t do it (BTW: I have used “form” check boxes rather than “control” check boxes, as I am not a programming type, and found these easy to implement).

I’d be very grateful indeed if anyone could suggest to me what I need to do so that making a drop-down list selection or changing a check box’s state will also trigger the autosort – I have attached the code that is attached to the worksheet (i.e. not a macro).

And while we’re at it, can anyone suggest why conditional formatting works strangely when its condition is the “TRUE / FALSE” associated with the check box. If I select the formula to be checked as an absolute reference (e.g. $g$2), it works fine – but if that row changes position, the source of the conditional format will then be wrong. And if I select it as a variable reference, only some of the cells take on the conditional formatting.

Here’s the code (the check boxes are in column F and the TRUE/FALSE in column G):

Private Sub Worksheet_Change(ByVal Target As Range)

Range(“A:G”).Sort Key1:=Range(“G2”), Order1:=xlAscending, Key2:=Range(“e2”), Order1:=xlAscending, Key3:=Range(“b2”), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

I also tried doing this check for changes to values in the check boxes or their TRUE/FALSE values using this:

If Target.Column 7 Then Exit Sub

Changing the target column value to any of the other columns worked fine – so I guess it operates on text changes only???

Thanks & best regards

Neil

Reply | Quote

Replies

Can you attach a small sample workbook? The data may be fake.

Reply | Quote

Hans

I should have thought to attach it to my original post. After a closer look, I’ve found that changing the “Priority” drop-down list selection (which is a sorting criteria) does trigger the sort automatically – it’s just the check box & its result that don’t. The “Re-sort” button at the bottom is connected to a macro that is a duplicate of the code in the worksheet – to get around my little problem.

As always, very grateful for your help.

Very best regards

Neil

Reply | Quote

You could assign the Sort macro to each of the check boxes (right-click a check box, select Assign macro from the popup menu, select …!Sort, click OK).

Reply | Quote

Hans

Once again, you’ve saved the day!

May I stretch a friendship and put two more questions to you?

1. I have tried applying conditional formatting on each row so that it changes appearance if marked “complete” – but clearly I’m doing something wrong. What sort of condition do I need to be testing for If I want to do this based on the contents of the true / false cell?

2. I’m always trying to learn to do this stuff better: can you suggest any simple change that would allow this to work using just one copy of the macro, rather than 2? Not a big deal, but always good to know,

Many thanks again

Neil

Reply | Quote

1. Select rows 2 through 19 (or as far down as needed). Cell A2 should now be the active cell within the selection.
Select Format | Conditional Formatting…
Select ‘Formula Is’ from the dropdown.
Type =$G2 in the box next to it.
Click Format… and set the formatting you want.
Click OK (twice).

2. You can call the Sort macro in the Worksheet_Change event procedure of the Action List sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
Call Sort
End Sub

If you change the code in Sort, the Worksheet_Change event will automatically use the modified code.

Reply | Quote

Hans

That’s got it – another excellent learning exercise for me!

Thanks and best regards

Neil

Reply | Quote