Deleting controls

S

Stan

Hi all

I'm still in the early stages of learning VB so please bear with me..

My sheet contains parts information which copied from the web. Each time a new order comes through, these parts are pasted into my spreadsheet. The problem is however that this also pastes some 'HTMLSelect' combo boxes just off my working area.

I want to be able to delete these 'HTMLSelect' boxes once all the data has been pasted into the sheet. I recorded a macro to see how the code would look for deleting 1 of them (shown below

ActiveSheet.Shapes("Control 85").Selec
Selection.Delet

The control number changes each time so

Is it possible to select all controls that relate to 'HTMLSelect' OR select each one in sequence

Any help on this would be much appreciated

Thanks

Sta
 
D

Dave Peterson

Do you have any other shapes on the worksheet (including comments)???

Option Explicit
Sub testme01()
'try to delete them all at once
ActiveSheet.Shapes.Delete
End Sub

Sub testme02()
'If there are lots, this may not work and you'd may have to do more:
Dim iCtr As Long
For iCtr = ActiveSheet.Shapes.Count To 1 Step -1
ActiveSheet.Shapes(iCtr).Delete
Next iCtr
End Sub

Sub testme03()
'I'm not sure what these shapes are (I don't touch the web very often!).
'But if they all had the same prefix:

Dim iCtr As Long
For iCtr = ActiveSheet.Shapes.Count To 1 Step -1
If LCase(Left(ActiveSheet.Shapes(iCtr).Name, 8)) = "control " Then
ActiveSheet.Shapes(iCtr).Delete
End If
Next iCtr
End Sub
 
S

sTAN

Dave

Thanks a million! I've used the testme03 code - it's spot on as I've several other shapes on the worksheet
Cheers

Stan
 
Top