PDA

View Full Version : Deleting Checkboxes



mferrisi
03-23-2007, 07:07 AM
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
03-23-2007, 07:32 AM
Not too sure about the default name of shapes but

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

You could always name them yourself...

mferrisi
03-23-2007, 07:51 AM
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
03-23-2007, 07:58 AM
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
03-23-2007, 08:06 AM
You should name them like this when you are creating them:


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


Xld posted you something like this in your other thread.

then to delete:



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


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

lucas
03-23-2007, 08:16 AM
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
03-23-2007, 08:18 AM
also eliminates the need to delete them...

csmith222
10-23-2017, 03:14 AM
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
10-23-2017, 04:43 AM
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
11-07-2017, 10:24 AM
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.