Check boxes part of cells

Hi Chris,

Checkboxes (really Checkbox control objects--either Forms or ActiveX controls) are objects that sit on top of a worksheet as an embedded object and can never be put INTO a cell. You can size the checkbox so that it sits right on top of a cell and set a property of the checkbox that causes it to resize and move with the cell.

However, if you just want to click on a cell, and have a check or X appear in that cell, you can do that with a Worksheet_SelectionChange event, using the event to trap the selection of a cell and entering a check (or removing it) when it is selected. You can get a check mark from the Wingdings font (you will have to format the cell for Wingdings format and write the checkmark into the cell with a Chr(254) or Chr(252) function since it is outside the keyboard range).

Damon

Chris,
Another thing you might consider is to use the linked cell property of the activeX control. If the cell has a value of True or False then the checkbox control will indicate that it is true or false. What I have done in the past, is to place the checkbox directly over the linked Cell That way it appears to be part of the worksheet. I can also use code to change the value of that particular cell and thus invoke the click event of the checkbox. Something like Range("A1").Value = "False".

Now if you want to cause the click event to fire without using the linked cell trick, you will need to refer to the checkbox in code. One way is easy but not flexible. The other way is flexible, but obfucated.

An Easy way. Use the sheet name and address the checkbox control like any other property / method.

Sub test()
Sheet1.CheckBox1.Value = Not (Sheet1.CheckBox1.Value)
End Sub

This will change the value and be like "clicking" the control.

Another way is to use the OLEObject collection.
This would be a way to refer to the control with a variable.

Sub test()
Sheet1.OLEObjects("Checkbox1").Object.Value = Not (Sheet1.OLEObjects("Checkbox1").Object.Value)
End Sub

This accomplishes the same thing. The only problem I have with using the OLEObjects collection is that you have to know what type of object you are dealing with in order to know how to access that object's properties. I have also seen the following being valid for some objects:
Sheet1.OLEObjects("ObjectName").Value instead of Sheet1.OLEObjects("ObjectName").Object.Value

Another alternative is to take it a step further and you can go to the validation sub menu (under the Data menu) change the validation criteria to allow a list, add TRUE,FALSE to the source and now you have the two choices as part of the cell.

You can hide the checkbox control if you want. When the user activates the particular cell, a drop down arrow appears. Clicking this arrow will present him with his choices and he can change the value. When that happens the click event will fire, because it is the linked cell for that checkbox control.

How does that sound?

Tuc