Excel Check Boxes, Even Simpler Way

Be- a- utiful !!.

I believe though, the while purpose of the thread was to programatically add as many checkboxes as you need. I believe there are 2 approaches (at least) to do so:
1.- ask first “how many checkboxes do you need?”, and then add them using a macro.
2.- evaulate the content of a cell within a range, and determine if it’s empty or not, and if not (assuming there you put the expression you want to evaluate, which eventually can be used as the checkbox caption), then add the corresponding checkbox with its relative format.

Rgds.

I didn’t write this, but this bit of code works to add as many check boxes as you’d like. Here’s the link: http://www.terminally-incoherent.com/blog/2008/09/04/excel-adding-checkboxes-the-easy-way/

I used it for a project where I had to insert hundreds of check boxes – it even names the check box by the cell it resides in, quite helpful.

When you run the macro, it pops up three dialog boxes, the first you enter the cells you want the check boxes (A1:A12); in the second you enter the column you want the corresponding control to go (”A” if you want them to sit under the check boxes, “B” if you want them next to them etc.); and the third is where you’d type a label. There may be a way to have each label be different (or correspond to a cell, but I never use labels…)

And here’s the code:

Sub insertCheckboxes()

Dim myBox As CheckBox
Dim myCell As Range

Dim cellRange As String
Dim cboxLabel As String
Dim linkedColumn As String

cellRange = InputBox(Prompt:=”Cell Range”, _
Title:=”Cell Range”)

linkedColumn = InputBox(Prompt:=”Linked Column”, _
Title:=”Linked Column”)

cboxLabel = InputBox(Prompt:=”Checkbox Label”, _
Title:=”Checkbox Label”)

With ActiveSheet
For Each myCell In .Range(cellRange).Cells
With myCell
Set myBox = .Parent.CheckBoxes.Add(Top:=.Top, _
Width:=.Width, Left:=.Left, Height:=.Height)

With myBox
.LinkedCell = linkedColumn & myCell.Row
.Caption = cboxLabel
.Name = “checkbox_” & myCell.Address(0, 0)
End With

.NumberFormat = “;;;”
End With

Next myCell
End With
End Sub

I don’t know how he did it ….. but I’d like to know because it’s a neat trick and that’s why people limit them to just a few at a time.
Puzzled.

There is another way to use Wingding chek boxes and change them with the double click with the help of macro

You can add as many of them by just defining the range in your macro. Values can be changed by just a double click.

You will furhter need to do data validation for that range so that user does not enter any other value by mistake.

Add following code to Sheet1 through VB Editor under microsoft excel obejects.

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

If Not Intersect(Target, Range(”G4:G14″)) Is Nothing Then
Cancel = True
If Target.Value = “þ” Then
Target.Value = “ý”

Else
Target.Value = “þ”
End If
End If

End Sub

Range G4:G14 will need to be formated with wingdings font.

You can download file containing this trick.
Change Value by double click.xls

This trick can be used to choose any values you want change with the double click and is not limited only to check boxes in wingdings.