Selecting objects for deletion in 2007

[Squeaky]

I have a spreadsheet with about 600 check boxes where I need to delete about
half of them.

In previous versions I could select as many objects as I chose by simply
drawing a box around them to select them and hitting delete.

It seems in 2007 I can either choose them all at once or one at a time,
nothing inbetween. Is this nightmare scenario correct or is there a way?

Squeaky

 

[Homey]

i not remember way you say delete boxs in old excels but this makro delets
check boxes in range you select first

Sub DelCkBoxes()
Dim Ck As CheckBox
For Each Ck In ActiveSheet.CheckBoxes
If Not Intersect(Ck.TopLeftCell, Selection) Is Nothing Then
Ck.Delete
End If
Next
End Sub


|I have a spreadsheet with about 600 check boxes where I need to delete
about
| half of them.
|
| In previous versions I could select as many objects as I chose by simply
| drawing a box around them to select them and hitting delete.
|
| It seems in 2007 I can either choose them all at once or one at a time,
| nothing inbetween. Is this nightmare scenario correct or is there a way?
|
| Squeaky

 

[Squeaky]

Hi Homey.

How do I select the range?

 

[Ron de Bruin]

Hi Squeaky

See
http://www.rondebruin.nl/controlsobjectsworksheet.htm

You can use this code example to delete all shapes

Sub Shapes1()
'Delete all Objects except Comments
On Error Resume Next
ActiveSheet.DrawingObjects.Visible = True
ActiveSheet.DrawingObjects.Delete
On Error GoTo 0
End Sub

Or only checkboxes from Forms
ActiveSheet.CheckBoxes.Delete

Or ActiveX checkboxes

Sub OLEObjects3()
Dim obj As OLEObject
For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSForms.CheckBox Then
obj.Delete
End If
Next
End Sub

 

[Homey]

say you want to delete all checks in range a1 to d12. first go to cell a1.
then drag with left mouse buton down to d12

| Hi Homey.
|
| How do I select the range?
|
| "Homey" wrote:
|
| > i not remember way you say delete boxs in old excels but this makro
delets
| > check boxes in range you select first
| >
| > Sub DelCkBoxes()
| > Dim Ck As CheckBox
| > For Each Ck In ActiveSheet.CheckBoxes
| > If Not Intersect(Ck.TopLeftCell, Selection) Is Nothing Then
| > Ck.Delete
| > End If
| > Next
| > End Sub
| >
| >
| > | > |I have a spreadsheet with about 600 check boxes where I need to delete
| > about
| > | half of them.
| > |
| > | In previous versions I could select as many objects as I chose by
simply
| > | drawing a box around them to select them and hitting delete.
| > |
| > | It seems in 2007 I can either choose them all at once or one at a
time,
| > | nothing inbetween. Is this nightmare scenario correct or is there a
way?
| > |
| > | Squeaky
| >
| >

 

[Homey]

i see rons code. my code is for checkbox from Forms toolbar. not work if
you used Control toolbox type check box sorry.

| Hi Homey.
|
| How do I select the range?
|
| "Homey" wrote:
|
| > i not remember way you say delete boxs in old excels but this makro
delets
| > check boxes in range you select first
| >
| > Sub DelCkBoxes()
| > Dim Ck As CheckBox
| > For Each Ck In ActiveSheet.CheckBoxes
| > If Not Intersect(Ck.TopLeftCell, Selection) Is Nothing Then
| > Ck.Delete
| > End If
| > Next
| > End Sub
| >
| >
| > | > |I have a spreadsheet with about 600 check boxes where I need to delete
| > about
| > | half of them.
| > |
| > | In previous versions I could select as many objects as I chose by
simply
| > | drawing a box around them to select them and hitting delete.
| > |
| > | It seems in 2007 I can either choose them all at once or one at a
time,
| > | nothing inbetween. Is this nightmare scenario correct or is there a
way?
| > |
| > | Squeaky
| >
| >

 

[Squeaky]

Hi Ron,

I'll give it a try. So basically the answer is MS removed the ability to
group selected objects using the mouse. It's either all or one at a time. I'm
not thrilled about having to introduce a macro into macro-free documents.
What if i only want to select them to move a bunch of them over a bit?

Squeaky.

 

[Ron de Bruin]

Manual it is not possible to select every type of shape (I not test it in SP2)
I send that bug a long time ago.

As far as I know code is the only way


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm