VBA - delete single shape of multiple

[captsolo0]

trying to clear one shape(macro button) from sheet and it is also deleting a picture I want to keep - macro section highlighted below

...
Sub CREATENEWINVOICE()

Dim INVNO As Long
Dim CUSTNAME As String
Dim AMT As Currency
Dim DT_ISSUE As Date
Dim TERM As Byte
Dim PATH As String
Dim FNAME As String
Dim NEXTREC As Range


INVNO = Range("M1")
CUSTNAME = Range("B8")
AMT = Range("M28")
DT_ISSUE = Range("L2")
TERM = Range("L4")
PATH = "C:\Users\User\Desktop\WORKORDERS\"


'C\Users\User\Desktop\Share\WORKORDERS\
FNAME = INVNO & " - " & CUSTNAME

'COPY IN THE WORKORDER SHEET TO A NEW WORKBOOK

Sheet1.Copy


'THEN DELETE ALL THE BUTTONS ON THE WORKSHEET - I have another shape/picture I want to keep but macro is delete the button and the picture

Dim SHP As Shape

For Each SHP In ActiveSheet.Shapes
SHP.Delete
Next SHP

'the picture is not in the cell range listed below

Range("B8").Validation.Delete

If Sheet1.Range("B8") = "" Then

Range("B7:E7,B9:E14").ClearContents
End If


'SAVE THE WORKBOOK TO A SPECIFIED FOLDER
With ActiveWorkbook
.Sheets(1).Name = "WORKORDERS"
.SaveAs Filename:=PATH & FNAME, FileFormat:=51
.Close
End With


'NEED TO PUT THE DETAILS OF THE WORKORDER IN THE RECORD OF WORKORDER SHEET

Set NEXTREC = Sheet3.Range("A1048576").End(xlUp).Offset(1, 0)

NEXTREC = INVNO
NEXTREC.Offset(0, 1) = CUSTNAME
NEXTREC.Offset(0, 2) = AMT
NEXTREC.Offset(0, 3) = DT_ISSUE
NEXTREC.Offset(0, 4) = DT_ISSUE + TERM

Sheet3.Hyperlinks.Add ANCHOR:=NEXTREC.Offset(0, 7), Address:=PATH & FNAME & ".XLSX"



Dim WRKORDNO As Long

WRKORDNO = Range("M1")

Range("M1:N1,B8:E8,A18:L27,A31:F33,G31:N33").ClearContents

MsgBox "YOUR NEXT INVOICE NUMBER IS " & WRKORDNO + 1

Range("M1") = WRKORDNO + 1

Range("B8").Select

ThisWorkbook.Save


End Sub

 

[Micron]

Your post and its title are a bit conflicting/confusing. You want to remove all command buttons from a sheet but leave everything else alone, or delete one command button and leave everything else? If one only, exclude it by referring to it by name. If all of a certain type, refer to them by type - shape type for command button is 8.
EDIT - 8 might not be correct; that needs to be checked out.

 

[captsolo0]

Micron,

Apologizes for the thread title. I tried to narrow down a paragraphed explanation with key words. I will do better going forward.

As it is apparent, I am in my infancy to writing macro code for Excel. Per the suggestion I have read through several help documents and all format attempts have failed.

I need help with formatting the code to remove the button. How should the code (below) be adjusted to reflect your explanation - my command button type is ( 1 ) by the way ( I did find that)



Dim SHP As Shape

For Each SHP In ActiveSheet.Shapes
SHP.Delete
Next SHP

 

[Micron]

In my experimentation, shape type and type name properties were of little value as several different shape types returned the same number. You didn't directly answer my questions so I'm just going to interpret your last post as if it's only one button to be deleted. In that case, use its name property. Try

For Each shp in ActiveSheet.Shapes
   If shp.Name = "shapeNameGoesHere" Then shp.Delete
Next

 

[captsolo0]

Apologizes again Micron for my lack of communication and/or assumptions. I appreciate your time.

I tried several “shape names” with no luck.

I did find in another thread from BeginnerB for an identical issue.

Tried the code below from DanteAmor with success.


For Each shp In ActiveSheet.Shapes
If shp.Type <> msoPicture Then
If shp.Type <> msoTextBox Then
shp.Delete
End If
End If
Next shp

 

[Micron]

Should have worked if you knew the name. If not, then you'd go to the Developer tab > Design Mode, right click on the control to open property sheet to get its name. If it's not there (can depend on type of control) then you right click and choose View Code and the code sheet will probably pick a default procedure to use but it will contain the shape name. What you have there will probably work until you forget and add another command button that you want to keep, but will also disappear.