ActiveX Checkboxes on Worksheet

[Marcster]

I'm self learning about Excel VBA http://vbaexpress.com/forum/images/smilies/001.gif and have a problem
with ActiveX Checkboxes on a worksheet. http://vbaexpress.com/forum/images/smilies/100.gif

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. http://vbaexpress.com/forum/images/smilies/think.gif
Although the sub does make all the checkboxes un-disabled, when you click on a checkbox again, the checkbox doesn't disable. http://vbaexpress.com/forum/images/smilies/doh.gif
Why?. http://vbaexpress.com/forum/images/smilies/102.gif

[Bob Phillips]

I'm self learning about Excel VBA http://vbaexpress.com/forum/images/smilies/001.gif and have a problem
with ActiveX Checkboxes on a worksheet. http://vbaexpress.com/forum/images/smilies/100.gif

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 :thumb , I'm getting to what I want.

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



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?.:think:

Thanks :thumb .

[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 :thumb .

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