Deleting Checkbox Object in Cell

[AndrewSiffert]

Hello,
I have not problem creating a checkbox with in a cell. However when it come
time to delete the checkbox I can't seem to select the object. Note, the
sheet has several checkboxes and deleting them by name will not work. I need
to reference a cell to delete the checkbox.

ActiveSheet.CheckBoxes.Delete

This works great for all cells but how about if I just want to delete one
checkbox in an selected cell.

Thanks,
Andy

[Mark Lincoln]

Right-click on the checkbox, then either select Cut from the popup
menu, or click on the checkbox's border and press the Delete key.

You'll need to unprotect your worksheet if it's protected.

[Norman Jones]

Hi Andrew,

Try:
'=============>>
Sub TestA()
Dim Cbox As CheckBox
Const sAddress As String = "A8" '<<==== CHANGE

For Each Cbox In ActiveSheet.CheckBoxes
If Cbox.TopLeftCell.Address(0, 0) = sAddress Then
Cbox.Delete
End If
Exit For
Next Cbox

End Sub
'<<=============


---
Regards,
Norman


"AndrewSiffert" <[email protected]> wrote in message
news:[email protected]...
> Hello,
> I have not problem creating a checkbox with in a cell. However when it
> come
> time to delete the checkbox I can't seem to select the object. Note, the
> sheet has several checkboxes and deleting them by name will not work. I
> need
> to reference a cell to delete the checkbox.
>
> ActiveSheet.CheckBoxes.Delete
>
> This works great for all cells but how about if I just want to delete one
> checkbox in an selected cell.
>
> Thanks,
> Andy

[Norman Jones]

Hi Andrew,

More flexible would be:

'=============>>
Sub TestB()
Dim Cbox As CheckBox
Dim Rng As Range

Set Rng = Range("A8, C8") '<<==== CHANGE

For Each Cbox In ActiveSheet.CheckBoxes
If Not Intersect(Cbox.TopLeftCell, Rng) Is Nothing Then
Cbox.Delete
End If
Next Cbox

End Sub
'<<=============

---
Regards,
Norman


"AndrewSiffert" <[email protected]> wrote in message
news:[email protected]...
> Hello,
> I have not problem creating a checkbox with in a cell. However when it
> come
> time to delete the checkbox I can't seem to select the object. Note, the
> sheet has several checkboxes and deleting them by name will not work. I
> need
> to reference a cell to delete the checkbox.
>
> ActiveSheet.CheckBoxes.Delete
>
> This works great for all cells but how about if I just want to delete one
> checkbox in an selected cell.
>
> Thanks,
> Andy

[AndrewSiffert]

Hello Again, I am new with VBA and I apricate anyone who offers help.
Lets first explain what I am trying to do:

Set up Excel:
In excel sheet1 create four check boxes place them in "E10", "E11",
"E12", "E13". These check boxes have no text, are in 3D and link to "G10",
"G11","G12", "G13" and check the box to make these cells read false.

The problem:
On row 12 incert a row
The problem is when I do this the link moves too the checked cell, but the
check box does not move down with that row. So I want to move the check box
down to the row with the linked cell. So, the Checkbox in "E12" needs to be
moved to "E13".


What can I write to either move this checkbox down a row.

What I tired is to create a new check box knowing wheather it is true or
false and than delete the old one in "E12". But I can't seem to get that to
work.

Current Code
Sub Trial1()
'Create Checkbox
Range("E13").Select
Dim A As String
A = Range("G13")
If A = False Then
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
.Value = xlOff
.LinkedCell = "g13"
.Display3DShading = True
.Characters.Text = ""
.Name = c.Address
End With
c.Select
Next
End If
'Delete old checkbox
''''Sample Code'''
Dim Cbox As CheckBox
Dim Rng As Range

Set Rng = Range("E12") '<<==== CHANGE

For Each Cbox In ActiveSheet.CheckBoxes
If Not Intersect(Cbox.TopLeftCell, Rng) Is Nothing Then
Cbox.Delete
End If
Next Cbox
End sub


"Norman Jones" wrote:

> Hi Andrew,
>
> More flexible would be:
>
> '=============>>
> Sub TestB()
> Dim Cbox As CheckBox
> Dim Rng As Range
>
> Set Rng = Range("A8, C8") '<<==== CHANGE
>
> For Each Cbox In ActiveSheet.CheckBoxes
> If Not Intersect(Cbox.TopLeftCell, Rng) Is Nothing Then
> Cbox.Delete
> End If
> Next Cbox
>
> End Sub
> '<<=============
>
> ---
> Regards,
> Norman
>
>
> "AndrewSiffert" <[email protected]> wrote in message
> news:[email protected]...
> > Hello,
> > I have not problem creating a checkbox with in a cell. However when it
> > come
> > time to delete the checkbox I can't seem to select the object. Note, the
> > sheet has several checkboxes and deleting them by name will not work. I
> > need
> > to reference a cell to delete the checkbox.
> >
> > ActiveSheet.CheckBoxes.Delete
> >
> > This works great for all cells but how about if I just want to delete one
> > checkbox in an selected cell.
> >
> > Thanks,
> > Andy
>
>
>

[Mark Lincoln]

To allow the checkboxes to move when you insert cells, right-click the
the checkboxes and click on "Format Control" on the pop-up menu. In
the window that follows, click on the "Properties" tab, then click the
radio button in front of "Move but don't size with cells." I'm
guessing they are currently set for "Don't move or size with cells."