checkbox value unavailable?

[Dr. Schwartz]

In Excel 2003 I have 12 control checkboxes (only 2 below in the code) in my
sheet. Depending on the value of the individual checkbox a piece of code
should run.

Public BoxId As Integer
Private Sub CheckBox1_Click()
BoxId = 1
UpdateDataLine
End Sub

Private Sub CheckBox2_Click()
BoxId = 2
UpdateDataLine
End Sub
Sub UpdateDataLine()
'BoxId = BoxId + 4145
Dim cbx As shape, cbx_val
For Each cbx In ActiveSheet.Shapes
If cbx.ID = BoxId + 4145 Then
If cbx.Value = True Then 'this one is causing me problems
ThisWorkbook.Worksheets("mysheet").Range(Cells(BoxId + 2,
1), Cells(BoxId + 2, 3)).Value = _
ThisWorkbook.Worksheets("mysheet").Range(Cells(BoxId + 2,
15), Cells(BoxId + 2, 17)).Value
End If
End If
Next
End Sub

My problem is that I can't find a way to determine the value of the given
checkbox. when i type cbx. I don't get the Value option in the dropdown list
that appear so I'm sure I'm doing something fundamentally wrong.

Is my code "the long way" to perform a simple thing or how can I make it work?

Anyone?

Thanks
The Doctor

 

[Joel]

Check boxes are shapes and they are objects. You can't get the value of the
box using the shape. Try code like this. Notice to get the value you need
to use OBJECT

For Each chkbx In ActiveSheet.OLEObjects
a = chkbx.Object.Value
Next chkbx

 

[Dave Peterson]

I think I'd drop going through the shapes collection and just pass the
UpdateDataLine sub the checkbox and the number you want:

Option Explicit
Private Sub CheckBox1_Click()
Call UpdateDataLine(CheckBox1, 1)
End Sub
Private Sub CheckBox2_Click()
Call UpdateDataLine(CheckBox2, 2)
End Sub
Sub UpdateDataLine(CBX As MSForms.CheckBox, BoxId As Long)
If CBX.Value = True Then
With ThisWorkbook.Worksheets("mysheet")
.Range(.Cells(BoxId + 2, 1), .Cells(BoxId + 2, 3)).Value _
= .Range(.Cells(BoxId + 2, 15), .Cells(BoxId + 2, 17)).Value
End With
End If
End Sub

And be careful with your ranges.

This kind of unqualified range (the cells() stuff) causes trouble:

ThisWorkbook.Worksheets("mysheet").Range(Cells(BoxId + 2, 1), Cells(BoxId + 2,
3)).Value

If this subroutine is in a general module, then the unqualified cells() will
refer to the activesheet.

But I'm betting that the sub is behind the worksheet. In that case, the
unqualifed cells() refer to the sheet that owns the code. And that may not be
MySheet.

And sometimes, you can refer to ranges so that it's a little easier to read.

With ThisWorkbook.Worksheets("mysheet")
.Cells(BoxId + 2, 1).Resize(1, 3).Value _
= .Cells(BoxId + 2, 15).Resize(1, 3).Value
End With

 

[Dave Peterson]

If shp is a checkbox from the control toolbox toolbar, I could use:

MsgBox shp.OLEFormat.Object.Object.Value

 

[Dr. Schwartz]

Thanks a lot Dave - you deliver as always !

I have applied your suggested code and it works perfectly. You're also quite
right that the code is placed in a worksheet, so I'm being careful to use the
correct reference.

I was not familiar with the Resize property - now I am and you're right
(again) that it is easier to work with.

The Doctor