check boxes + autofilter - is it possible?

[Dirk Diggler]

hello,

I'd like to use Excel in automation mode to create a worksheet in which
consecutive columns of a row contain 1) a checkbox and 2) a label. I am
a beginner in this subject and all I have done so far is only filling "a
label" column using a code similar to:

oE = new ActiveXObject('Excel.Application');
oW = oE.Workbooks.Add();
oW.Activate();
for(i=1;i<n;i++) {
..
oW.Worksheets(1).Cells(i,2).value = Label(i);
..
}

I have two questions:
1) Is it possible "to link" a check box with appropriate "label" cell
within the same row, in a way which allows to use autofilter on "label"
column, and which make "linked" check box to disappear as label is
filtered out, and appear again when a label is not filtered?

2) Is it possible to do this with VBA or JScript, and how?

I would greatly appreciate your help and suggestions. Any
link/example/www resource/tutorial would be very helpful.

thanks,
DD.

 

[Dave Peterson]

How about an (easier) alternative.

Instead of using a checkbox (from the control toolbox toolbar) and having to
struggle with size, placement, linked cell, you could just use a cell.

Select the range
Format|cells|number tab|custom category
In the "type:" box, put this:
alt-0252;alt-0252;alt-0252;alt-0252

But hit and hold the alt key while you're typing the 0252 from the numeric
keypad.

It should look something like this when you're done.
ü;ü;ü;ü
(umlaut over the lower case u separated by semicolons)

And format that range of cells as Wingdings.

Now, no matter what you type (spacebar, x, anyoldtextatall), you'll see a check
mark.

Hit the delete key on the keyboard to clear the cell.

If you have to use that "checkmark" in later formulas:

=if(a1="","no checkmark","Yes checkmark")

 

[Dirk Diggler]

Dave, this is an interesting solution, very nice in its simplicity. I
like it, thank you. But I would have to adapt it further. Do you know by
a chance a way I could operate over those cells with mouse clicks ie. a
way to change a content of a cell from a check mark into empty and vice
versa?

thanks,
DD

 

[Dave Peterson]

Not by a single mouse click.

But you could tie into the rightclick or doubleclick.

If you want to try, rightclick on the worksheet tab that should have this
behavior. Select view code and paste this into the code window:

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub

Cancel = True 'stop editing in cell

target.value = "x"
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
Cancel As Boolean)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub

Cancel = True 'stop pop up from showing

Target.value = ""
End Sub

I used column A in my code--change the range to what you want.

===
You could just use one event and toggle the contents--like:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
Cancel As Boolean)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub

Cancel = True 'stop pop up from showing

if Target.value = "" then
target.value = "x"
else
target.value = ""
end if
End Sub

 

[Dirk Diggler]

Dave, this is very smart and beautiful solution. I greatly appreciate
your help.

Thank you!
DD.

 

[Dave Peterson]

It's still possible to do all you want with checkboxes from the Control
toolbox.

But there's an added level of complexity--and you may find that with lots of
these checkboxes (I'm not sure how many make up "lots", though), the workbook
can do some weird things.