Programmatically format a cell as a checkbox

Question

I'm developing an Excel addin that will acquire data from a webservice before constructing a workbook.

In the example workbook I've been given as part of the requirement some of the cells are 1 or empty. These are SUMmed and the result stored elsewhere.

I don't especially like this and I'm trying to think of a way to use checkboxes for these fields but still keep the cell values as 1 or 0 (for checked and unchecked).

This gives me a few questions:

1. Can a checkbox be added and configured so that checking it gives the value 1 and unchecking it gives 0 to the associated cell?
2. Can this be done programmatically from within an Excel addin?

Using my extensive research capabilities (Google) I found that I can add a checkbox and associate it with a cell so that checking/unchecking sets the cell value to TRUE or FALSE. If this is the only option I could probably deal with it (sigh!) but can this be done from c# code in an addin?

Answer 1

This is an example using cell B9

It is implemented using Double-click rather than Single-click

Enter the following event macro in the worksheet code area:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim B9 As Range
    Set B9 = Range("B9")
    If Intersect(B9, Target) Is Nothing Then Exit Sub
    B9.Font.Name = "Marlett"
    Cancel = True
    If B9.Value = 1 Then
        B9.Value = 0
        B9.NumberFormat = ";;;"
    Else
        B9.Value = 1
        B9.NumberFormat = Chr(34) & "a" & Chr(34) & ";;;"
    End If
End Sub

Double-clicking B9 will toggle the check.

Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

NOTE:

It is possible to implement an event using the standard left button click, but it is a bit more code.

Answer 2

I found this very helpful. I turned the code around so that I could specify a large number of cells as tick boxethat could each be ticked while maintaining the underlying values:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim TickBoxes As Range

'Specify the range of cells you want to be tickboxes

Set TickBoxes = Union(Range("A1"), Range("B2:D8"), Range("E9:G15")) 'etc

If Intersect(TickBoxes, Target) Is Nothing Then Exit Sub

Target.Font.Name = "Marlett"
Cancel = True

If Target.Value = 1 Then
    Target.Value = 0
    Target.NumberFormat = ";;;"
Else
    Target.Value = 1
    Target.NumberFormat = Chr(34) & "a" & Chr(34) & ";;;"
End If

End Sub

Not that clever, but hopefully of use to someone!

Alan K