Delete a command button via VBA

[Brian K. Sheperd]

Is there a way to delete a command button? I have an excel template file
with multiple sheets. We have older files that are older revisions. I want
the user to open the template file and open an old file. On the template
file, I have a button that will open a user form showing the open excel
files so that they can select the desired file and it will copy the
appropriate data from the old file to the new template, rename the old file,
and save the template with the name of the old file. I wanted to delete the
command button so that it will only appear on the template instead of every
file. I guess that I could hide the button based on a value that I place on
the sheet, but I figured that I would just try to remove it.

Thanks,
Brian

 

[Tom Ogilvy]

Dim obj as OleObject
for each obj on Activesheet.OleObjects
if type of obj.Object is MSForms.CommandButton
then obj.Delete
end if
Next

if you only have one shape on the sheet (no autofilter dropdowns or other
shapes)

ActiveSheet.Shapes(1).Delete

this should help you find the best solution for you.

 

[Brian K. Sheperd]

Tom,
Thank you. I will give it a shot.
Thanks,
Brian