Using checkboxes to sort data

Re: Using checkboxes to sort data

Hi Jennie,

Several questions would need to be asked in order to clarify your constraints ...

or ... much easier ... why don't you attach a worksheet sample ... :smile:

Re: Using checkboxes to sort data

Hi Jennie,

Thanks a lot for your sample worksheet ...

Congratulations on your smart conditional formatting ...!!!

Once a given number of checkboxes are ticked ... what would be precisely your objective ?

Re: Using checkboxes to sort data

Rather than all those checkboxes (I assume your working version will have many more rows than your example), I prefer to use Martlett Checkboxes. (Note that your attachment is 60K, the one without the checkboxes is 26K)

Double click on a cell in column B to put an X (or remove it)

I think this might do what you are looking for.

Re: Using checkboxes to sort data

It is VBA event code driven by the BeforeDoubleClick event.

Re: Using checkboxes to sort data

They are declared in the procedure...

Dim sourceValues As Variant, otherValues As Variant
    Dim otherPlace As Range

A 'Variant' is a non-specific type of variable - it can refer to anything. Contrast with

Dim strName as String
Dim OtherPlace as Range

Which can only be a String and a reference to a Range of cells on a worksheet, respectively.

A little later, SourceValues is set to refer to a range of cells and OtheValues simply a copy of SourceValues

sourceValues = .CurrentRegion.Value
            otherValues = sourceValues

Near the top of the procedure, OtherPlace is set to refer to cell G1

Set otherPlace = Range("G1")

And near the End, OtherPlace is resized to the same size as the array SourceValues, that range is cleared and then populated with the contents of OtherValues.

otherPlace.Resize(1, UBound(sourceValues, 2)).EntireColumn.ClearContents
           otherPlace.Resize(Pointer, UBound(sourceValues, 2)).Value = otherValues

Even though the code 'switches' between SourceValues and OtherValues, they are exactly the same size, the only difference is OtherValues (remember, both are arrays) contains the sorted values so that is what is written back to the worksheet.

Re: Using checkboxes to sort data

Thanks cytop for the complete explanation of the VBA code ... :smile:

Should Jennie be willing to keep the tick off symbol, she could use ü and the wingdings font ...

Cheers

Re: Using checkboxes to sort data

Do you mean you only want the Double click to be trapped in column 2 of the range A18:F30?

Re: Using checkboxes to sort data

There are two Location codes in that routine.
The first is the test

With Target
        If .Column = 2 Then

Which asks "is the double clicked cell in column B, if so, treat it like a checkbox"

One could alter the test to

If .Column = 2 and 17 < .Row Then

which would make only cells B18 and below the "checkbox cells"

If .Column = 2 and 17 < .Row And .Row < 31 Then

would make only cells B18:B30 the "checkbox cells"

The other location line is the one that sets the whole source range.

Set SourceRange = Target.CurrentRegion

If you leave a blank row and column on each side of the source range, then there is no need to alter that line.
Alternantly you could explicitly set the source range to what you want

Set SourceRange = Range("A18:F30")

Re: Using checkboxes to sort data

Quote from mikerickson;760970

Rather than all those checkboxes (I assume your working version will have many more rows than your example), I prefer to use Martlett Checkboxes. (Note that your attachment is 60K, the one without the checkboxes is 26K)

Double click on a cell in column B to put an X (or remove it)

I think this might do what you are looking for.

I know this thread is a couple months old, but I could really use some help if you're willing.

I am looking for something similar to this, but I can't seem to interpret the code enough to make the changes I need to make. I don't have any coding skills, so a lot of it just doesn't make sense. I tried to make some sense of your further explanations in this thread on sourceValues/otherValues/etc., but I couldn't figure it out. I need where it's pasted (otherPlace?) to be on a different sheet, and I don't want the column that is checked (the Martlett Checkboxes?) visible in the pasted section. Ideally, I would move the checkbox column to column A, then have it copy/paste the rest of the row into another pre-made sheet (don't need it to auto-create the sheet). Thanks in advance for any direction.

Re: Using checkboxes to sort data

Hi,

To be on the safe side, and sure to get your customized solution ... why don't you attach your excel file in your next message ...

HTH

Re: Using checkboxes to sort data

Quote from Carim;763206

Hi,

To be on the safe side, and sure to get your customized solution ... why don't you attach your excel file in your next message ...

HTH

I'm actually just playing around with the TickboxExample2 in post 6 of this thread, trying to figure out how to modify the code. What I'm trying to do is set up a sheet with a bunch of football plays, and then when those plays are checked on sheet1, they will be copied to sheet2. Kind of like a game sheet that will be changing weekly. Like I said, I'm not real savvy when it comes to coding, but I can usually figure some of the easier stuff out. My complete lack of knowledge in visual basic is showing through on this, though. I usually accomplish everything using formulas, but that just won't cut it for this.