Select a CheckBox from another worksheet

Question

I have a ActiveX CheckBox control on worksheet "A" and another on worksheet "B". When I check the CheckBox at "A", I want my macro to check the CheckBox at "B".

What I have tried so far:

This: Sheets("B").Shapes("CheckBox1").ControlFormat.Value = xlOn

And this: ThisWorkbook.Worksheets(1).Shapes("Check Box 1").OLEFormat.Object.Value = 1

Both codes give me an error saying that the the object doesn't accept this property or method.

So it's not possible to check a CheckBox from another worksheet?

score 5 · Answer 1 · answered Nov 21 '15 at 12:18

I find it useful to use a With ... End With statement to reference a worksheet as it allows multiple operations.

With Worksheets("B")
    ' for Form Control Checkbox
    .Shapes("Check Box 2").ControlFormat.Value = xlOn
    ' for ActiveX Control Checkbox
    .Shapes("CheckBox1").OLEFormat.Object.Object.Value = xlOn
End With

The prefix period (aka . or full stop) applies the parent worksheet.

score 3 · Accepted Answer · answered Nov 21 '15 at 12:16

3

Checkbox1 in sheet 1 to change checkbox1 in sheet 2

Private Sub CheckBox1_Click()
    If Me.CheckBox1 = True Then
        Sheets("Sheet2").CheckBox1.Value = True
    Else
        Sheets("Sheet2").CheckBox1.Value = False
    End If
End Sub

answered Nov 21 '15 at 12:16

Davesexcel

6,896
2
27
42

score 0 · Answer 3 · answered Feb 15 '18 at 21:38

For me, replacing the numbers 1 and 0 as for, respectively, the boolean "True" and "False" did not work. Some say that ActivexControl has "False" equal to -4146 and FormControl has "False" as equal to 0. This did not work either. The snipped below is now working.

Dim i as Integer

For i = 1 to 502

  If Sheets("Sheet1").Shapes("Alpha-" & i).ControlFormat.Value = 1 Then '<-- FormControl

    Sheets("Sheet2").Shapes("beta" & i).OLEFormat.Object.Object.Value = 1 '<--ActivexControl
  Else
     Sheets("Sheet2").Shapes("beta" & i).OLEFormat.Object.Object.Value = 0 '<--ActivexControl
  End If

next i

Select a CheckBox from another worksheet

3 Answers3

Linked