Check Box Value

[Herd]

I am using the following code to create checkboxes and have the value offset.
How can I get the checked value to show "1" instead of "TRUE" in the offset
cell and to always show a blank ("") in the offset cells in which the
checkbox is not checked instead of "FALSE"?

Thanks for the help. It is appreciated.

Dim myCBX As CheckBox
Dim myCell As Range


With ActiveSheet
.CheckBoxes.Delete
For Each myCell In ActiveSheet.Range("B3:B800").Cells

With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
.LinkedCell = myCell.Offset(0, 10).Address(external:=True)
.Caption = ""
.Name = "CBX_" & myCell.Address(0, 0)
End With
.NumberFormat = ";;;"
End With

Next myCell
End With

 

[Dave Peterson]

If you're going to use a linked cell, maybe you could hide that linked cell and
use a formula that points at it:

=if(b3=true,1,"")

If you don't want to use a formula, you could drop the linked cell and assign a
macro to each of those checkboxes.

Option Explicit
Sub testme()

Dim myCBX As CheckBox
Dim myCell As Range

With ActiveSheet
.CheckBoxes.Delete
For Each myCell In ActiveSheet.Range("B3:B800").Cells

With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
'.LinkedCell = myCell.Offset(0, 10).Address(external:=True)
.Caption = ""
.Name = "CBX_" & myCell.Address(0, 0)
.OnAction = "'" & ThisWorkbook.Name & "'!dothework"
End With
.NumberFormat = ";;;"
End With

Next myCell
End With
End Sub

Sub DoTheWork()
Dim myCBX As CheckBox
Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)

If myCBX = xlOn Then
myCBX.TopLeftCell.Offset(0, 10).Value = 1
Else
myCBX.TopLeftCell.Offset(0, 10).Value = ""
End If

End Sub