Check Boxes in Cells

[Tony]

I would like to have check boxes in cells to display any expression that
evaluates to True (checked) or False (unchecked).

And, I would like to change the cells' value to be "True" or "False" by
clicking the box - one in each cell.

I don't think this is the job for the check box control.

Can this be done?

 

[J.E. McGimpsey]

A checkbox from the Forms toolbar will do exactly this. In the
Format checkbox dialog, set the cell link to your target cell.
Clicking the checkbox will toggle the cell's value between True and
False. Entering 0 in the linked cell will clear the checkbox;
entering a non-zero value will put a check in it.

 

[Tony]

Thanks Bob, this works fine.

However I can't figure out how to make it work on the active cell.
For example if you click on a cell by mistake you have to move off it first
before toggling it back.

Any clues?

cheers
Tony

 

[J.E. McGimpsey]

Well, you could use something like this:

Public Sub LotsOfCheckboxes()
Dim cell As Range
For Each cell In Range("B1:B100")
With cell
With ActiveSheet.CheckBoxes.Add(.Left, .Top, 50, 20)
.Value = xlOff
.LinkedCell = cell.Address
.Display3DShading = False
End With
End With
Next cell
ENd Sub

Substitute your range of cells and position to suit.

 

[Tony]

HA!

That's cool
Thanks J.E.

Well, you could use something like this:

Public Sub LotsOfCheckboxes()
Dim cell As Range
For Each cell In Range("B1:B100")
With cell
With ActiveSheet.CheckBoxes.Add(.Left, .Top, 50, 20)
.Value = xlOff
.LinkedCell = cell.Address
.Display3DShading = False
End With
End With
Next cell
ENd Sub

Substitute your range of cells and position to suit.

 

[jenova314]

Well, you could use something like this:

Public Sub LotsOfCheckboxes()
Dim cell As Range
For Each cell In Range("B1:B100")
With cell
With ActiveSheet.CheckBoxes.Add(.Left, .Top, 50, 20)
.Value = xlOff
.LinkedCell = cell.Address
.Display3DShading = False
End With
End With
Next cell
ENd Sub

Substitute your range of cells and position to suit.


In article ,
"Tony" wrote:

> I tried it and it worked. There's about 100 entries in the sheet.
> I guess I would have to create a separate control for every cell ?

Thanks for the great tip. I was wondering if anybody else could help me out with an additional revision.

After creating a few hundred check boxes, I realized that it's using an "Alternative Text," which I do not want. Consequently, I have to edit each one manually. Shouldn't I be able to input the parameters into the macro? I tried, and it failed. Here's what I had:

Public Sub LotsOfCheckboxes()
Dim cell As Range
For Each cell In Range("D4200")
With cell
With ActiveSheet.CheckBoxes.Add(.Left, .Top, 20, 20)
.AlternativeText = ""
.Value = xlOff
.LinkedCell = cell.Address
.Display3DShading = False
End With
End With
Next cell
End Sub

The red line is the offending script that causes the macro to fail. Any help would be greatly appreciated!