problems with autofilter and checkboxes

[Joan Broers]

Hallo,

I use Excel 2007

I want to use autofilter on a worksheet with a column with checkboxes. The
filtering is set on a different column.

The problem is: the checkboxes are not properly hidden in rows that are
filtered out. They are shown on top of each other in a row that is shown.

How do I solve this problem?



Thanks Joan

 

[Dave Peterson]

There are two types of checkboxes you can use in excel.

One is from the Forms toolbar. The second is from the Control toolbox toolbar.

If you used the checkboxes from the control toolbox toolbar, you can:

Click on the Design Mode icon on that control toolbox toolbar
right click on each of the checkboxes (one at at time!)
Choose Format Control
Click on the properties tab
Make sure the "move and size with cells" is checked.
and finish up the dialog.
And then leave design mode (use that icon on the control toolbox toolbar)



=====
If you used checkboxes from the Forms toolbar, then this property isn't
available. You'd have to use another way to hide them -- and those won't be pretty!

============
(saved from a previous post)

I like this technique:

Select the range that would have held the checkboxes.
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 (make it as large as you want)

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

And you can use:
=counta(a1:a10)
to find the number of "checks" in A1:A10.

 

[GS]

After serious thinking Joan Broers wrote :

Hallo,

I use Excel 2007

I want to use autofilter on a worksheet with a column with checkboxes. The
filtering is set on a different column.

The problem is: the checkboxes are not properly hidden in rows that are
filtered out. They are shown on top of each other in a row that is shown.

How do I solve this problem?



Thanks Joan

This may seem a bit unconventional but it works well with AutoFilter...

In place of using controls for checkboxes, I have sized a column and
placed borders around cells that I use for checkboxes to give the
'appearance'. The column is formatted to use Monotype Sorts font so by
entering '3' in the cell it displays a checkmark. (Entering '4'
displays a bold checkmark). Since this is usually associated with a VBA
project I can monitor changes to that column so that any input other
than '3' or '4' is revised to '3' or '4'. (I usually give instruction
to enter '3' only, and distribute this font with the project.)

 

[Joan Broers]

Hallo Dave,

Thank you for your answer.
You are right with the ActiveX-checkbox and the right properties it works
with the autofilter much better then with the form-one.
There is only another problem now. The value of the checkbox is used in a
formula. (If linked cell =true etc.)
With the ActiceX checkbox I have to write vba code to put the value in a
cell. This is easy programming, however there are many checkboxes on the
worksheet, so that means a lot of copying and pasting. Is there an easier
way to use the value of the checkbox in a formula?

Joan.

 

[Joan Broers]

Hallo Garry,

Thank you for your answer. It looks interesting. The vba code will be no
problem.
However I do not know anything of Monotype Sorts font. Do I have to download
it from internet or is it part of MS Office?
Do you use a special one or is Monotype Sorts font a special one. I had a
quick look at internet, but did not understand it fully.
I do hope you can tell me more about it.

Joan.

 

[Dave Peterson]

You don't need to use code with either checkbox.

But the way you assign the linked cell is different between the Forms toolbar
checkbox and the Control toolbox checkbox.

With the control toolbox checkbox.
Go into design mode
rightclick on the checkbox
choose Properties
Look for the .linkedcell property
type in the address you want to use

Don't forget to exit design mode.

=====
I still like the font and format trick. It looks like a checkmark and you can
use the cell itself (not a linked cell).

=====
If you lots of checkboxes from the control toolbox toolbar, you can use code to
assign the linked cell.

I'd use something like:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim OLEObj As OLEObject

Set wks = Worksheets("Sheet1")

For Each OLEObj In wks.OLEObjects
With OLEObj
If TypeOf .Object Is MSForms.CheckBox Then
.LinkedCell = .TopLeftCell.Address(external:=True)
'or some cell (like to the left one column???)
'.LinkedCell _
= .TopLeftCell.Offset(0, -1).Address(external:=True)

'hide the true/false in the sheet
'but it's still visible in the formula bar
.TopLeftCell.NumberFormat = ";;;"
End If
End With
Next OLEObj

End Sub

 

[GS]

After serious thinking Joan Broers wrote :

Hallo Garry,

Thank you for your answer. It looks interesting. The vba code will be no
problem.
However I do not know anything of Monotype Sorts font. Do I have to download
it from internet or is it part of MS Office?
Do you use a special one or is Monotype Sorts font a special one. I had a
quick look at internet, but did not understand it fully.
I do hope you can tell me more about it.

Joan.

I'm pretty sure Monotype Sorts font came with an earlier version of
MSO. Probably v2000 as I've been using it since XL9. Mind you, I did
purchase a shareware disk of fonts back then and so it may have been
included in that bundle. I found where you can get it for free, though:


http://www.free-fonts-ttf.org/true-type-fonts/monotype-sorts-9931-download.htm

 

[Joan Broers]

Hallo Garry,



I found the font on the internet.

The problem will be the distribution of the font.

But an 'x' instead of a tick can do aswel.



Joan

 

[Joan Broers]

Thanks Dave,

With a control checkbox I cannot find a property Linked Cell.

But the vba solution is a good alternative.

By your method I do not need to distribute a font, what makes it easier for
home users.

A disadvantage is that you have to run the macro if you add an extra
checkbox.



Thank you (and Garry) for the two fine solutions..



Joan

 

[Dave Peterson]

Did you choose Properties after you right clicked?

 

[GS]

Joan Broers explained :

Hallo Garry,



I found the font on the internet.

The problem will be the distribution of the font.

But an 'x' instead of a tick can do aswel.



Joan

I'm glad you were able to get the font. Yes, using an 'x' works too but
I do like the checkmark. Sometimes I'll ask users to use a rating from
1 to 'n', which must be selected from a DV dropdown. (I also use a DV
dropdown for the checkmark)


<distribution>
I don't have a problem with distributing the font since I include it in
the package when I distribute the project. I'm not sure why you'd have
a problem with distributing the font since you don't say there's a
problem with distributing your project.<g> I use self-extracting zips
or setup EXEs.

The only issue is how the font gets installed in Windows. In the case
of using a ZIP, I send a ReadMe.txt (which opens after the unzip) that
explains how to add a font to the Windows Fonts Library. This is very
easy since the Fonts folder under Windows has a menuitem to add new
fonts.

In the case of a Setup.EXE the fonts are installed automatically.

HTH

 

[Joan Broers]

You are right, I hadnot looked at the proerties.
So all he problems are solved.
Thanks