Reference a check box in another open workbook

Question

I am trying to refer to the value of an ActiveX checkbox control in another worksheet. My goal is to make the value of the check box in my current workbook the same as the one in another open workbook.

I can do this between two checkboxes in the same workbook on different sheets:

Private Sub CommandButton1_Click()

If Sheets("Sheet2").Box2.Value = True Then
    Box1.Value = True
Else: Box1.Value = False
End If

End Sub

But I'm receiving a Run-time error '9' "Subscript out of range" error when I run the following code:

Private Sub CommandButton2_Click()

If Worksheets("Book2").OLEObjects("Box3").Value = True Then
    Box1.Value = True
Else: Box1.Value = False
End If

End Sub

The "If Worksheets" line is highlighted when I try to debug the code. I'm sure I'm referring to the other checkbox incorrectly, but I've searched high and low for the proper way to refer to this without any luck. Thank you in advance for your help!

Andy G Andy G · Accepted Answer · 2014-05-16T19:56:31

If Worksheets("Book2").OLEObjects("Box3").Value = True Then

"Book2" is not the name of a Worksheet, presumably it is the name of the other book. In which case:

If WorkBooks("Book2").Worksheets(1).OLEObjects("Box3").Value = True Then

..or use whatever the name of the worksheet in the other book is called.

Reference a check box in another open workbook

1 Answers