Delete a button in a worksheet from macro

[macro_user]

hi...
i have a basic sheet say sheet1 which has a button say button1 which invokes a macro... now, i need to copy sheet1 and generate a report in it... so, i copy the sheet1 from my macro and paste it at the end and renaming it (all from the macro)... i then generate the report... but in this report, there's this button1 in the new sheet which i do not want... how do i delete the button from the macro???

 

[Datsmart]

How about setting the properties on the original button to not allow it to be copied along with your data?

Right click your button:
Choose "Format."
On Properties tab, check "Don't move or size with cells".

 

[macro_user]

Nope... tat isn't working... is there any other way? like cells(1,"C").clearcontents or clearformats or anything?

 

[GTO]

Greetings macro_user,

You fail to say whether the "button" is from the Forms toolbar or Control Toolbox. Presuming an ActiveX CommandButton, maybe something like:

Sub ex()
Dim wksNew As Worksheet
    
    ThisWorkbook.Worksheets("Sheet1").Copy _
        After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
    
    Set wksNew = ActiveSheet

    wksNew.Name = "NewSheet"
    wksNew.Shapes("CommandButton1").Delete
End Sub

Hope this helps,

Mark

 

[macro_user]

gr8.. the copy paste part is perfect... and i apologize for not telling wat type of button it is... it's a Form Control button... does the same thing work for both types of controls??? Form or ActiveX?

 

[GTO]

As to deleting a button, if you just have a button or two, yes - using Shapes(index or name) should work. To find the name of the button, right-click on it, and look in the name box.

   '...code...
 
wksNew.Shapes("Button 1").Delete
End Sub

 

[macro_user]

gr8... it works!!! Thank you...