Delete check boxes automatically

[jcvanderhorst]

Currently I'm facing the following difficulty:

I have added several checkboxes in Column A. But if I want to delete
them all, I have to select them one by one, right mouse click and
press <delete>

Does someone know a simple way of deleting those checkboxes all in one
action?

Look forward to your reply, thanks in advance,

Johan

 

[Dave Peterson]

If they're from the forms toolbar:
activesheet.checkboxes.delete

If they're from the control toolbox toolbar:
Dim myCBX As OLEObject
For Each myCBX In Activesheet.OLEObjects
If TypeOf myCBX.Object Is MSForms.CheckBox Then
myCBX.Delete
End If
Next myCBX

 

[Johan]

Tnx Dave, but it doesn't work yet.

I use the following Macro to add the checkboxes to a selected area:

Sub chckbxmkr()

On Error Resume Next
Dim c As Range, myRange As Range
Set myRange = Selection
For Each c In myRange.Cells
ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width, c.Height).Select
With Selection
..LinkedCell = c.Address
..Characters.Text = ""
..Name = c.Address
End With
c.Select
With Selection
..FormatConditions.Delete
..FormatConditions.Add Type:=xlExpression, _
Formula1:="=" & c.Address & "=TRUE"
..FormatConditions(1).Font.ColorIndex = 6 'change for other color when
ticked
..FormatConditions(1).Interior.ColorIndex = 6 'change for other color
when ticked
..Font.ColorIndex = 2 'cell background color = White
End With
Next
myRange.Select
End Sub

But if I run your code I get an error on this:
"TypeOf myCBX.Object Is MSForms.CheckBox"

Can you help out? Tnx again,

Johan

 

[Dave Peterson]

You added checkboxes from the Forms toolbar.

Don't use that second suggestion. Use the first.

 

[Johan]

Aaah...I was reading too quickly. It works really good. Thanks for your
nice and quick help!

Johan