Delete check boxes automatically
 

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

Delete check boxes automatically
 

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

wrote:

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

Delete check boxes automatically
 

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

Delete check boxes automatically
 

You added checkboxes from the Forms toolbar.

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

Johan wrote:

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

Delete check boxes automatically
 

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

Johan