testing the state of a checkbox in vba code

[Paul James]

I have a checkbox named CheckBox1 in a worksheet named "DataEntry," and I
would like to be able to test the state of this checkbox in VBA. That is, I
would like to test to see whether it's checked or not.

Can anyone tell me how to do this?

Thanks in advance.

Paul

 

[Rocky McKinley]

Right Click on the checkbox and
select format control then
select properties
assign a cell link, this cell link will be changed each time the checkbox is
selected and de-selected.
You can refer to it as need be with formulas or VB.

 

[Felix]

Paul,
if the Checkbox is a Form Control not a VB Control the
following Code should work.
This code will actually go for the Checkbox Text Label
(as it appears to the user) not for the Name in VBA.
Felix

Sub Read_FormControls()
Dim MyShape, CheckBox1Value


For Each MyShape In ActiveSheet.Shapes
If MyShape.Type = msoFormControl Then
If MyShape.FormControlType = xlCheckBox Then
If MyShape.AlternativeText="CheckBox1" then
If MyShape.ControlFormat.Value = 1 Then
CheckBox1Value=True
Else
CheckBox1Value=False
End if
End If
End If
End if
Next

For i = 1 To 8
If DlgEntries.Controls.item("chbx_FrmBox" + CStr
(i)).Caption = "N/A" Then
DlgEntries.Controls.item("chbx_FrmBox" + CStr
(i)).Visible = False
ii = ii + 1
End If
Next

End Sub

 

[Paul James]

Thanks for your reply, Rocky.

I do know how to establish a cell link with the check box. However, the
problem I'm having is that I can't get my VBA test statements, that is, the
If statements, to work by referring to the values TRUE and FALSE which
appear in the linked cell.

For example, I've tried the following If statement:

If Worksheets("dataentry").Range("G3") = True Then
MsgBox "true"
Else
MsgBox "false"
End If

When the box is checked, the linked cell displays the value TRUE; when
unchecked, it displays the value FALSE. But regardless of the status of the
check box, (and so no matter what the value of the linked cell), the MsgBox
always displays "false" when I run this sub.

So my problem isn't how to link a checkbox to a linked cell, it's how to
interpret those TRUE and FALSE values in the linked cell.

Any idea on how to modify the If conitions in the VBA code above to get it
to correctly respond to the values in the linked cell?

Thanks again in advance.

Paul

 

[Paul James]

Thanks for the reply, Felix.

I tried your code and couldn't get it to work for me. I was also puzzled by
the sentence in your message that said:

"This code will actually go for the Checkbox Text Label
(as it appears to the user) not for the Name in VBA. "

I was puzzled by this because I'm trying to test to see whether the checkbox
is checked. It doesn't help my purpose to be considering the Text Label.

Again, I'm looking for the VBA code I would use in a VBA "If" statement to
tell me whether a check box is checked.

Can anyone help me with this?

Thanks.

 

[Vasant Nanavati]

But regardless of the status of the

check box, (and so no matter what the value of the linked cell), the MsgBox
always displays "false" when I run this sub.

That can't be right. Perhaps you are testing the wrong cell.

 

[Paul James]

Vasant, you're right!

It was a typo, and when I checked again, I realized that, like you said, I
was testing the wrong cell.

Thanks to Rock, Vasant and Felix for their suggestions and replies to this
message.

Paul

 

[lcoreyl]

*Right Click on the checkbox and
select format control then
select properties
assign a cell link, this cell link will be changed each time th
checkbox is
selected and de-selected.
You can refer to it as need be with formulas or VB.
*

Is there a way to change which cell you're linked to in VB code

 

[lcoreyl]

*Right Click on the checkbox and
select format control then
select properties
assign a cell link, this cell link will be changed each time th
checkbox is
selected and de-selected.
You can refer to it as need be with formulas or VB.
*

Is there a way to change which cell you're linked to in VB code

 

[lcoreyl]

Is there a way to change which cell the box is linked to within VB code

 

[James Cox]

Doing it the "old-fashioned" way of turning the macro recorder on and
recording the operation gave

ActiveSheet.Shapes("Check Box 3").Select
With Selection
.LinkedCell = "$F$13"
End With

Good luck !

James

Is there a way to change which cell the box is linked to within VB code?


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to

creating financial statements