Marlett checkboxes

http://blog.livedoor.jp/andrewe/#top

The new version of our utilities is almost complete. In the meantime, I have been working on some new stuff. Something that I had fun with was a way to make working with Marlett checkboxes easier, a new addin.



Select a range and push Add Formatted Checkboxes to Range and the colors shown above will appear. Choose a color and the checkboxes will be added to the selected cells. At the same time, the code to make the checkboxes work will be added to your workbook so it will be okay even if you uninstall the addin at a later date, the code inside the workbook will run independently. You can also change the checkbox colors by selecting the same cells and choosing a new color.

Other options - Select, Delete, Tick and Untick require you to select cells that contain checkboxes as shown below (Don't select the shapes above the checkbox cells, they merely act as a way to activate the code to show the checkoxes as ticked or unticked)



Note: I've tested on Excel 97, 2003 and 2007 and it works okay. Depending on your security settings, you many need to ensure access to the Visual Basic Project is enabled. Instructions to do so are shown in the addin Read Me file.

Code for the addin is unlocked so you can see how things work. If you want to experiment working with the Visual Basic Editor, you will need to set a reference to the Visual Basic Project - make sure the project you are working on is selected and go to Tools, References and check Microsoft Visual Basic for Applications Extensibility on the VBE, (not the Tools Menu within Excel itself, the Visual Basic Editor!)

You can download it here :-)

CUSTOM FUNCTION APPROACH (Autofilter)

http://www.ozgrid.com/News/nov-2006.htm

CUSTOM FUNCTION APPROACH

The 1st one we will use is a custom Excel function. This will not only flag the correct criteria field but also tell us what the criteria is. Let's assume the table to filter occupies A1:E1000 with A1:E1 being headings. First we should always have at least 3 rows above any table in Excel. Select rows 1:3 and go to Insert>Rows. This will insert 3 rows, or as many as you select. We will use at least 1 row to display the criteria in the appropriate column. Why at least 3 rows then? Good practice is the answer. This way we can set up criteria, if need be, for Advanced Filter.

CRITERIA FUNCTION CODE

Below is the code that must be added to the Workbook, or an Excel Add-in. To add the code to a Workbook go to Tools>Macro>Visual Basic Editor (Alt+F11) then to Insert>Module and paste in the code below;

Function AutoFilter_Criteria(Header As Range) As String
Dim strCri1 As
String, strCri2 As String
Application.Volatile
With
Header.Parent.AutoFilter
With .Filters(Header.Column - .Range.Column + 1)
If Not .On Then Exit Function
strCri1 = .Criteria1
If .Operator =
xlAnd Then
strCri2 = " AND " & .Criteria2
ElseIf .Operator = xlOr
Then
strCri2 = " OR " & .Criteria2
End If
End With
End With

AutoFilter_Criteria = _
UCase(Header) & ": " & strCri1 &
strCri2
End Function

Note the use of Application.Volatile in the code. This will ensure our function updates whenever the AutoFilter criteria changes. Ok, ensure you have AutoFilter applied to your table. If you don't, the function will return #VALUE! Now, in, say A1 Enter =AutoFilter_Criteria(A4) (A4 is the 1st heading) and copy across as many columns as you have headings. Now simply filter by any criteria and the function will display your criteria in the relative column cell.