Delete a command button via VBA

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

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.

--
Regards,
Tom Ogilvy

"Brian K. Sheperd" <brians.remove@remove.lesker.com> wrote in message
news:%237HMNeCbFHA.3384@TK2MSFTNGP09.phx.gbl...
> 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,
Thank you. I will give it a shot.
Thanks,
Brian

"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:eDGMLWDbFHA.2664@TK2MSFTNGP15.phx.gbl...
> 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.
>
> --
> Regards,
> Tom Ogilvy
>
> "Brian K. Sheperd" <brians.remove@remove.lesker.com> wrote in message
> news:%237HMNeCbFHA.3384@TK2MSFTNGP09.phx.gbl...
> > 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
> >
> >
>
>