PDA

View Full Version : Solved: Delete Command Button



artds
12-16-2007, 08:15 AM
Hi there! Just a quick check. I put a Command Button in an Excel Sheet, "Monthly Report", in which it will copy this sheet along with another sheet, "Variables", and save it in a new workbook.

However, the Command Button will be copied as well. So is there anyway in VBA that I can delete the command button from the new workbook?

here are the codes from the Main workbook which have the command Button to generate that report


Sub GenReport()
Dim DistiName As String
Dim filename As String
Dim MyMonth As Variant

Application.ScreenUpdating = False
MyMonth = Application.Proper(MonthName(Month(Date)))
Sheets("report").Activate
DistiName = Sheet23.Cells(5, 1)
Sheets(Array("Variables", "Report")).Copy
Sheets("Variables").Visible = xlVeryHidden
filename = ThisWorkbook.Path & "\" & DistiName & "-" & MyMonth & ".xls"
Application.CalculateFullRebuild
DeleteLinks_Selection
ActiveWorkbook.Sheets("Report").Range("A5").Validation.Delete
CommandButton1.Delete
ActiveWorkbook.SaveAs filename
Application.ScreenUpdating = True
End Sub


However when the code reach the line "commandbutton1.delete", they prompt me an error , "Run time '424' Object Required". Anybody willing to help?



regards
artds

Bob Phillips
12-16-2007, 08:46 AM
Activesheet.Buttons("Button 1").Delete

artds
12-16-2007, 06:51 PM
Hi xld! Thanks for your quick response! :yes
Initially, i tried using your suggestion, but it didn't work. VBA prompt me an error, but from that point, I experimented on some other codes.

so heres the code that works for me,


ActiveSheet.Shapes("CommandButton1").Delete

where "CommandButton1" is the name of that command button. Thanks xld for your direction.:yes


regards
artds