Deleting Checkboxes

[mferrisi]

I have a program that creates check boxes depending on previous selecting made in the sheet. When the program is re-run, at the start, I need all of the check boxes that popped up during the previous run to be deleted.

When the program runs, I first clean up all previously generated data

Worksheets(1).Select
Range(Cells(27, 1), Cells(37, 19)).Clear
Cells(22, 5).ClearContents
'Here, I need to clear the generated checkboxes



Here is how they were created in the code.

Dim rst As Integer
Dim stu As Integer
Dim bcd As Integer
Dim Num_Bnchmrks As Integer
Num_Bnchmrks = 4
stu = 0
For rst = 1 To m
Do While bcd < Num_Bnchmrks
With Worksheets(1)
Set cb = .Shapes.AddFormControl(xlCheckBox, 270, 313 + stu * 16, 100, 10)
cb.ControlFormat.LinkedCell = Cells(22, 22 + bcd)

End With
bcd = bcd + 1
stu = stu + 1
Loop
bcd = 0
Next rst

[moa]

Not too sure about the default name of shapes but

[vba]For x = 1 To 4
Worksheets(1).Shapes("CheckBox " & x).Delete
Next[/vba]

You could always name them yourself...

[mferrisi]

Thanks Glen,

The only problem is that the checkboxes are getting up there in number, ie we are at Check Box 171 already, and the program is used quite often. Is there a way to reset the check box numbers?

When the checkboxes are created, I do give them a new name
ActiveSheet.Shapes("Check Box 172").Select
Selection.Characters.Text = "WhyWontThisWork"

Is there a way to delete them by looking at this name?

[lucas]

There is probably a better way to do this. Why exactly are you creating the checkboxes on the fly. Would it be possible to use a checkmark in the column instead of an actual checkbox?

[moa]

You should name them like this when you are creating them:

[vba]
With Worksheets(1).Shapes.AddFormControl(xlCheckBox, 270, 313 + stu * 16, 100, 10)
.name = "cbx" & bcd + 1
.ControlFormat.LinkedCell = Cells(22, 22 + bcd)
End With
[/vba]

Xld posted you something like this in your other thread.

then to delete:

[VBA]

For x = 1 To 4
Worksheets(1).Shapes("cbx" & x).Delete
Next

[/VBA]

BTW, as Lucas is saying, creating and deleting stuff on the fly is almost never a good idea.

[lucas]

I would get rid of the checkboxes altogether and use checkmarks which don't have to be added progrmatically and don't add bulk to the workbook....see attached

[lucas]

also eliminates the need to delete them...

[csmith222]

This is more than a decade later, but just in case anybody else stumbles across this as I did, then you might consider this:

Dim obj As OLEObject 
   For Each obj In ActiveSheet.OLEObjects
        If TypeOf obj.Object Is msforms.CheckBox Then
            obj.Delete
        End If
    Next

This will specifically target activex checkboxes, leaving the other controls alone and it does not make you name them some specific way, although I am working on auto-naming mine, so they can be controlled with macros without having to manually name them or recreate the macros. Anyway, my purpose was similar to you, that I wanted to generate checkboxes, based on cell values that change and I needed a way to clear the board and start fresh. I also programmed the linked cells, the captions, and which ones would be visible. Let me know if you're also trying to figure that out and I'll post the code I found for it.

[Aflatoon]

This will specifically target activex checkboxes, leaving the other controls alone

That's not strictly true. It will also delete any OptionButtons or ToggleButtons, since they also implement the Checkbox interface. You'd be better off using TypeName if you want to restrict specifically to Checkboxes:

If TypeName(obj.Object) = "CheckBox" Then

[csmith222]

That's not strictly true. It will also delete any OptionButtons or ToggleButtons, since they also implement the Checkbox interface. You'd be better off using TypeName if you want to restrict specifically to Checkboxes:

If TypeName(obj.Object) = "CheckBox" Then

Hey, that's good to know! Thanks for the heads up on that! I didn't realize it was necessary to make a deeper distinction to avoid messing with other controls.