Is it possible...to sort CHECK BOXES

[rbraxton]

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

 

[Damon Ostrander]

Sort checkboxes

Hi rbraxton,

What do you mean? Do you want to sort them by checked/unchecked status? By the control's object name? By the caption text?

Also, are the checkboxes on a userform or on a sheet?

Damon

 

[rbraxton]

The boxes are on a sheet.
Can I assign each box to a cell and sort the cells?

 

[Damon Ostrander]

sorting checkboxes

Hi again rbraxton,

Okay, it sounds like you don't really want to sort the checkboxes themselves, but rather to sort on, say, column A while checkboxes that are "in" column D stay with the rows as they are re-arranged by the sort. I put "in" in quotes because checkboxes can't really reside in cells, but are objects that can only reside on top of a worksheet.

If what I described is what you want to do, the checkboxes will "sort" with the rows if you include the cells underneath the checkboxes in the range to be sorted. This is true whether you sort manually or via VBA macro. So in the example above where the data are in cells A1:D12, simply select cells A1:D12, then do the sort on column A. If the checkboxes are ActiveX checkboxes (i.e., from the Controls toobar) then the Placement property must be set to 2 (which is the default) for them to sort with the underlying cells. If the checkboxes are from the Forms toolbar they must be formatted using the format control Properties tab for the "Move but don't size with cells" option, but this is also the default.

 

[rbraxton]

Thanks Damon for the guidance. (your right, I just want to sort the rows by any criterion, not necessarily the [/b]checkboxes themselves.)

I tried using the check boxes from the CONTROLS Toolbar, but didn't work too well.

Then I used the check boxes from the FORMS Toolbar, went to Format Control, then Control tab, and assigned each respective cell/check box in the Cell Link field.
That's working well; I can sort them now, but I have the words (TRUE or FALSE) behind each box now....how do I get rid of the words. (I could change the color of the text to match the words, making it seem as though they're not there, but I'm also using Conditional Formatting, so that's a 'no-go')

(Note: I wonder if there is a faster way to assign each check box to its respective cell without doing it one-by-one?)

Thanks,
Rob
[/u]

 

[Damon Ostrander]

A sordid--I mean sorted--affair

Hello again Rob,

You shouldn't have to assign the checkbox values to cells (which gives the TRUE/FALSE values) in order to get them to sort with the cells. I verified this in Excel 2000 for both Forms and Controls checkboxes--they sort just fine for me with no cell link. But as I said before, you must sort the entire range underlying the checkboxes, and the move-with-cells property of each checkbox must be set. If part of a checkbox is not within the sorted range it will not move with the sort.

Damon