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

Delete a command button via VBA
 

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" wrote in message
...
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

Delete a command button via VBA
 

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

"Tom Ogilvy" wrote in message
...
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" wrote in message
...
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