ActiveX Checkboxes on Worksheet

[Marcster]

I'm self learning about Excel VBA and have a problem
with ActiveX Checkboxes on a worksheet.

I have some checkboxes on a worksheet ("Sheet1").
When a checkbox is checked I want it to be disabled.
I have a button on the worksheet which when clicked will un-disable
all the checkboxes.

See sample code below:

Private Sub CheckBox1_Click()
Me.CheckBox1.Enabled = False
Application.ActiveWorkbook.RefreshAll
Range("D2").Select
End Sub
Private Sub CommandButton1_Click()
Dim objCkBox As Object
With Sheets("Sheet1")
For Each objCkBox In .OLEObjects
If TypeName(objCkBox.Object) = "CheckBox" Then
objCkBox.Object.Value = False
objCkBox.Object.Enabled = True
Application.ActiveWorkbook.RefreshAll
End If
Next objCkBox
End With
End Sub

The CheckBox1_Click sub works fine, the CommandButton1_Click sub doesn't work quite as I want it to.
Although the sub does make all the checkboxes un-disabled, when you click on a checkbox again, the checkbox doesn't disable.
Why?.

[Bob Phillips]

I'm self learning about Excel VBA and have a problem
with ActiveX Checkboxes on a worksheet.

I have some checkboxes on a worksheet ("Sheet1").
When a checkbox is checked I want it to be disabled.
I have a button on the worksheet which when clicked will un-disable
all the checkboxes.

See sample code below:

Private Sub CheckBox1_Click()
Me.CheckBox1.Enabled = False
Application.ActiveWorkbook.RefreshAll
Range("D2").Select
End Sub
Private Sub CommandButton1_Click()
Dim objCkBox As Object
With Sheets("Sheet1")
For Each objCkBox In .OLEObjects
If TypeName(objCkBox.Object) = "CheckBox" Then
objCkBox.Object.Value = False
objCkBox.Object.Enabled = True
Application.ActiveWorkbook.RefreshAll
End If
Next objCkBox
End With
End Sub

The CheckBox1_Click sub works fine, the CommandButton1_Click sub doesn't work quite as I want it to.

You need to check the object, not the object container

Private Sub CommandButton1_Click()
    Dim objCkBox As Object
    With Sheets("Sheet1")
        For Each objCkBox In .OLEObjects
            If TypeName(objCkBox.Object) = "CheckBox" Then
                objCkBox.Object.Value = False
                objCkBox.Object.Enabled = True
                Application.ActiveWorkbook.RefreshAll
            End If
        Next objCkBox
    End With
End Sub

[Marcster]

Hi xld, thanks for replying.

The code you have posted is the same code though.
I think I know what you mean though.

So, I have 10 checkboxes on sheet. Instead of writing:

Me.CheckBox1.Value = False
Me.CheckBox1.Enabled = True

for every Checkbox. How can I shorten the amount of code to write?.
How do I write the loop?.

Thanks.

[Bob Phillips]

The code you have posted is the same code though.
I think I know what you mean though.

Where was I at?

I think I have it though now.

Private mBarEvents As Boolean

Private Sub CheckBox1_Click()
    If Not mBarEvents Then
        mBarEvents = True
            Me.OLEObjects("CheckBox1").Object.Enabled = False
            Application.ActiveWorkbook.RefreshAll
            Range("D2").Select
        mBarEvents = False
    End If
End Sub

Private Sub CommandButton1_Click()
Dim objCkBox As Object
    If Not mBarEvents Then
        mBarEvents = True
            With Sheets("Sheet2")
            For Each objCkBox In .OLEObjects
                If TypeName(objCkBox.Object) = "CheckBox" Then
                    objCkBox.Object.Value = False
                    objCkBox.Object.Enabled = True
                End If
            Next objCkBox
        End With
        mBarEvents = False
    End If
    Application.ActiveWorkbook.RefreshAll
End Sub

[Marcster]

Thanks for that , I'm getting to what I want.

I'm just wondering how I can shorten this bit of code:

Me.CheckBox1.Value = False
Me.CheckBox1.Enabled = True
Me.CheckBox2.Value = False
Me.CheckBox2.Enabled = True
Me.CheckBox3.Value = False
Me.CheckBox3.Enabled = True
Me.CheckBox4.Value = False
Me.CheckBox4.Enabled = True
Me.CheckBox5.Value = False
Me.CheckBox5.Enabled = True
Me.CheckBox6.Value = False
Me.CheckBox6.Enabled = True
Me.CheckBox7.Value = False
Me.CheckBox7.Enabled = True
Me.CheckBox8.Value = False
Me.CheckBox8.Enabled = True
Me.CheckBox9.Value = False
Me.CheckBox9.Enabled = True

I reckon a loop is what I'm after.
But how do I replace the number (1 to 9) of the checkboxes with a varible?.

Thanks .

[Bob Phillips]

I'm just wondering how I can shorten this bit of code:

<snip>

I reckon a loop is what I'm after.
But how do I replace the number (1 to 9) of the checkboxes with a varible?.

Given up with the other approach?

Private Sub CommandButton1_Click()
Dim i As Long
    If Not mBarEvents Then
        mBarEvents = True
        With Sheets("Sheet1")
            For i = 1 To 9
                With .OLEObjects("CheckBox" & i)
                    .Object.Value = False
                    .Object.Enabled = True
                End With
            Next i
        End With
        mBarEvents = False
    End If
    Application.ActiveWorkbook.RefreshAll
End Sub

[Marcster]

Hi xld,

That now works like I wanted it too.
Thanks for all your help .

Marcster.

[gibbo1715]

is it possible to do the same kind of loop for this bit of code to cover all 9 checkboxes from one piece of code?

Private Sub CheckBox1_Click()
Dim objCkBox As Object
    If Not mBarEvents Then
        mBarEvents = True
        Me.OLEObjects("CheckBox1").Object.Enabled = False
        Application.ActiveWorkbook.RefreshAll
        Range("D2").Select
        mBarEvents = False
    End If
End Sub

[Bob Phillips]

is it possible to do the same kind of loop for this bit of code to cover all 9 checkboxes from one piece of code?

Private Sub CheckBox1_Click()
Dim objCkBox As Object
    If Not mBarEvents Then
        mBarEvents = True
        Me.OLEObjects("CheckBox1").Object.Enabled = False
        Application.ActiveWorkbook.RefreshAll
        Range("D2").Select
        mBarEvents = False
    End If
End Sub

You need a pseudo-control array for this.

First add a class module, rename it clsActiveXEvents, and add this code, suitably adjusted to what you want to do

Option Explicit

Public WithEvents mCheckboxes As MSForms.CheckBox

Private Sub mCheckboxes_Click()
Dim iCb As Long
    If mCheckboxes.Value Then
        MsgBox mCheckboxes.Caption & " turned on"
    Else
        MsgBox mCheckboxes.Caption & " turned off"
    End If
End Sub

and put this code in the worksheet with the checkboxes

Option Explicit

Dim mcolEvents As Collection

Private Sub Worksheet_Activate()
Dim cCBEvents As clsACtiveXEvents
Dim shp As Shape
Set mcolEvents = New Collection
For Each shp In Me.Shapes
        If shp.Type = msoOLEControlObject Then
            If TypeOf shp.OLEFormat.Object.Object Is MSForms.CheckBox Then
                Set cCBEvents = New clsACtiveXEvents
                Set cCBEvents.mCheckboxes = shp.OLEFormat.Object.Object
                mcolEvents.add cCBEvents
            End If
        End If
    Next
End Sub