Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Sep 2001
    Location
    Perth, Western Australia
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using a check box to trigger an autosort (2003 SP2

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using a check box to trigger an autosort (2003 SP2

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

  3. #3
    Star Lounger
    Join Date
    Sep 2001
    Location
    Perth, Western Australia
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a check box to trigger an autosort (2003

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using a check box to trigger an autosort (2003

    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).

  5. #5
    Star Lounger
    Join Date
    Sep 2001
    Location
    Perth, Western Australia
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a check box to trigger an autosort (2003

    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using a check box to trigger an autosort (2003

    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 <code>=$G2</code> 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.

  7. #7
    Star Lounger
    Join Date
    Sep 2001
    Location
    Perth, Western Australia
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a check box to trigger an autosort (2003

    Hans

    That's got it - another excellent learning exercise for me!

    Thanks and best regards

    Neil

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •