Clear CheckBoxes using VBA

[Casey]

Greeting and Happy Holidays to all,
I have a workbook which contains a Master Log. On that sheet is
command button which copies another worksheet in the Workbook, clean
out the previous data from the new copy, making it ready for new dat
entry.
On this Worksheet that gets copied there are 10-15 Checkboxes. The
perform no function other than to mark certain choices. Specifically
they are not tied to a Macro or a VBA routine.

What I would like to do is simply reset them so they are al
"unchecked" in the new copy. I have tried recording a macro whil
manually unchecking the boxes in an attempt to get an idea about how t
code this, but all the macro recorder picks up is any scrollin
activity. Also, this was a form I "inherited" so some of the CheckBoxe
are from the FORMS toolbar and some from the CONTROL TOOLBOX. I'm happ
to make them all the same, but if I could choose I like the formattin
capabilities of the CheckBox from the CONTROL TOOLBOX better.

Below is the code I currently use to copy and paste the worksheet int
which I would like to add the code to "Uncheck" the Checkboxes.

Sub Add_New_RFI()
Application.ScreenUpdating = False
Sheets("(1)").Copy After:=Sheets(ThisWorkbook.Sheets.Count)
With ActiveSheet

.Range("I10:K12,J16:K16,E20:K20,B21:K29,D39:K39,B40:K49").ClearContents
.Range("I10:K10").Select
End With
Application.ScreenUpdating = True
End Su

 

[Bob Phillips]

Casey,

Here is some code to clear control toolbox checkboxes


'-----------------------------------------------------------------
Sub ClearCheckboxes()
'-----------------------------------------------------------------
Dim i As Long

For i = 1 To ActiveSheet.OLEObjects.Count
If TypeName(ActiveSheet.OLEObjects(i).Object) = "CheckBox" Then
ActiveSheet.OLEObjects(i).Object.Value = False
End If
Next i

End Sub
'-----------------------------------------------------------------


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Casey]

Bob,
Thank you for the help. But I'm getting the following error message.

Run-time error "438"
Object doesn't support this property or method

Here is what I did:
I placed the code you provided in it's own module.
Then I used the "CALL" function in my exisitng code like this:

Sub Add_New_RFI()
Application.ScreenUpdating = False
Sheets("(1)").Copy After:=Sheets(ThisWorkbook.Sheets.Count)
With ActiveSheet

.Range("I10:K12,J16:K16,E20:K20,B21:K29,D39:K39,B40:K49").ClearContents
.Range("I10:K10").Select
Call ClearCheckboxes
End With
Application.ScreenUpdating = True
End Sub

The de-bugger points to the line in the code you provided that reads:

For i = 1 To ActiveSheet.OLEObject.Count

I have tried just running code you provided separately and still ge
the same error at the same place.

What am I doing wrong

 

[Casey]

Bob,
My mistake. I left off the "s" for OLEObjects when I typed in you
code. It works beautifully now. Can't thank you enough