Not Working: Code to Clear ActiveX Check Boxes

[pawcoyote]

Hi,

The code to provide the msgbox and to clearContents is working fine but the ActiveX Checkboxes are not clearing when I run the macro. All the ActiveX Checkboxes have been renamed so they are not the default. Any thoughts or suggestion on what I might have done wrong?

Sub Clear_COVERPAGE()

Set wsCoverPage = Worksheets("CoverPage")

If MsgBox("Are you SURE you want to clear all the data on " & wsCoverPage.Name & "???", vbQuestion + vbYesNo + vbDefaultButton2, "Clear " & wsCoverPage.Name) = vbNo Then Exit Sub

Dim c As Object
For Each c In ActiveSheet.OLEObjects
If InStr(1, c.Name, "INSTALL") > 0 Then
c.Object.Value = False
End If
Next

Range("C4", "C8").ClearContents
Range("C10", "C13").ClearContents
Range("C15", "C19").ClearContents
Range("C21", "C24").ClearContents
Range("F4", "F6").ClearContents
Range("E8", "F13").ClearContents

End Sub

[Paul_Hossler]

Stripping down to just the ActiveX controls, this works the way I'd expect

I have 3 ActiveX checkboxes, INSTALL1, INSTALL2, and INSTALL3.

Make sure you've named yours in upper case if that's what you're testing with InStr and that there are no spaces in the name



Option Explicit

Sub MakeFalse()
Dim wsCoverPage As Worksheet
Dim c As Object

Set wsCoverPage = Worksheets("CoverPage")

For Each c In ActiveSheet.OLEObjects
If InStr(1, c.Name, "INSTALL") > 0 Then
c.Object.Value = False
End If
Next

End Sub




Sub MakeTrue()
Dim wsCoverPage As Worksheet
Dim c As Object

Set wsCoverPage = Worksheets("CoverPage")

For Each c In ActiveSheet.OLEObjects
If InStr(1, c.Name, "INSTALL") > 0 Then
c.Object.Value = True
End If
Next
End Sub

[snb]

Sub Clear_COVERPAGE()
For Each it In sheets("CoverPage").OLEObjects
if InStr(ucase(it.Name), "INSTALL") then it.object=false
Next
End sub
But probably more robust:

Sub Clear_COVERPAGE()
For Each it In sheets("CoverPage").OLEObjects
if typename(it.object)="CheckBox" then it.object=false
Next
End sub

BTW:


Range("C4","C8","C10","C13","C15","C19","C21","C24","F4","F6","E8","F13").ClearContents

This code is an indication that the strructure of your worksheet needs improvement.
The more the structure of your data resembles a proper database structure the more likely the code only has to remove/delete 1 'record'.

[p45cal]

if typename(it)="CheckBox"
Needs to be
if typename(it.object)="CheckBox"


BTW:


Range("C4","C8","C10","C13","C15","C19","C21","C24","F4","F6","E8","F13").ClearContents

This will error, probably needs to be:
Range("C4:C8,C10:C13,C15:C19,C21:C24,F4:F6,E8:F13").ClearContents

[pawcoyote]

Hi p45cal,

The Msg and Clear.Contents works fine I get an error on the " it " when I try to run the clear checkbox. Does the "Install" need to be in all Caps or can it be as the Sheet is named? Thank you "All" for helping!!


Sub Clear_COVERPAGE()

Set wsCoverPage = Worksheets("CoverPage")

If MsgBox("Are you SURE you want to clear all the data on " & wsCoverPage.Name & "???", vbQuestion + vbYesNo + vbDefaultButton2, "Clear " & wsCoverPage.Name) = vbNo Then Exit Sub

For Each it In Sheets("CoverPage").OLEObjects
If TypeName(it.Object) = "CheckBox" Then it.Object.false


Next

Range("C4:C8,C10:C13,C15:C19,C21:C24,F4:F6,E8:F13").ClearContents

End Sub

[Paul_Hossler]

Did you read the notes and try the code in #2?

I'd probably make the change suggested and include a test for TypeName just in case



Option Explicit


Sub MakeFalse()
Dim wsCoverPage As Worksheet
Dim c As Object

Set wsCoverPage = Worksheets("CoverPage")

For Each c In ActiveSheet.OLEObjects
If InStr(1, c.Name, "INSTALL") > 0 Then
If TypeName(c.Object) = "CheckBox" Then c.Object.Value = False
End If
Next

End Sub




Sub MakeTrue()
Dim wsCoverPage As Worksheet
Dim c As Object

Set wsCoverPage = Worksheets("CoverPage")

For Each c In ActiveSheet.OLEObjects
If InStr(1, c.Name, "INSTALL") > 0 Then
If TypeName(c.Object) = "CheckBox" Then c.Object.Value = True
End If
Next
End Sub

[snb]

Please copy correctly:

it.Object = false

[pawcoyote]

Hi

I added in the False statement into my existing code, but it doesn't uncheck the boxes. The code does show the message box and clears the data though. I use Modules to store my code as well to try and keep it clean. I was trying to have to only use one button to run the Clear data. So, I must be doing something wrong.

I cleared out all other pages and such and attached a sample of the Coverpage.


Option Explicit'
' Clear Data from Cover Page Worksheet
'
Sub Clear_COVERPAGE()
' Start of Pauls Code


Dim wsCoverPage As Worksheet
Dim c As Object

Set wsCoverPage = Worksheets("CoverPage")

If MsgBox("Are you SURE you want to clear all the data on " & wsCoverPage.Name & "???", vbQuestion + vbYesNo + vbDefaultButton2, "Clear " & wsCoverPage.Name) = vbNo Then Exit Sub
'
' snb Code
'
' For Each it In Sheets("CoverPage").OLEObjects - Failes on "it"
' If TypeName(it.Object) = "CheckBox" Then it.Object = False

Next
'
' Pauls Code
'
For Each c In ActiveSheet.OLEObjects
If InStr(1, c.Name, "INSTALL") > 0 Then
If TypeName(c.Object) = "CheckBox" Then c.Object.Value = False
End If
Next

' Code provided byp45cal
'
Range("C4:C8,C10:C13,C15:C19,C21:C24,F4:F6,E8:F13").ClearContents

End Sub




29645

[snb]

Comment out 'option explicit' and run again.

[pawcoyote]

Did that still didn't remove the Checkbox. The message and data removed fine.

[snb]

Never use merged cells.
Do not group unnecessarily.


Sub M_snb()
On Error Resume Next

For Each it In Sheet1.OLEObjects
If TypeName(it.Object) = "CheckBox" Then it.Object = False
Next

Sheet1.Cells.SpecialCells(2).ClearContents
End Sub
If you persist in 'grouping' you need overcomplicated code like:

Sub M_tst()
For Each it In Shapes(1).GroupItems
it.OLEFormat.Object.Object = False
Next
End Sub
Or more generally:

Sub M_snb()
For Each it In Shapes
If it.Type = 6 Then
For Each it1 In Shapes(1).GroupItems
it1.OLEFormat.Object.Object = False
Next
End If
Next
End Sub

[Paul_Hossler]

Did that still didn't remove the Checkbox. The message and data removed fine.


Do you want to 'clear' the checkbox (i.e. = False) or 'Remove' the checkbox (i.e. = Delete)?

[pawcoyote]

Want to clear the box. It is part of a clear data macro for the worksheet.

[p45cal]

As snb said, do not group unnecessarily.
You have grouped the 4 checkboxes on the sheet and this, surprisingly to me, takes them out of the oleobjects collection on the sheet:
With them grouped, the following line leaves nothing in the ObjCollection:

Set ObjCollection = ActiveSheet.OLEObjects
but guess what?, you can still gain access to the checkboxes directly as children of the sheet; this works:

Set myCheckBox = ActiveSheet.OLEObjects("INSTALL") checkboxes grouped or not.

In your sheet, you have the shape Group 4 grouping the 4 checkboxes, so you could gain access:

For Each cbShp In ActiveSheet.Shapes("Group 4").GroupItems
cbShp.OLEFormat.Object.Object.value = FALSE 'there MUST be a shorter way…
Next cbShp

This could afford you a different way of resetting checkboxes if you have more than one group of them, but I wouldn't recommend that (there are other ways).
For now, I'd just ungroup them.

[pawcoyote]

This worked after I ungrouped it. Very cool. Will play around with the grouping items you mentioned p45cal!! Thank you, snb and Paul!!


Sub Clear_COVERPAGE() For Each it In sheets("CoverPage").OLEObjects
if typename(it.object)="CheckBox" then it.object=false
NextEnd sub