Remove a button from a worksheet

[Lazhal]

I created a macro to copy a worksheet. The source worksheet contains a button to run the macro. So, when I copy the worksheet, the button gets copied along with any data. I would like to hide (or even better, remove) this button from the destination worksheet after copying it.

Does anyone know how to refer (in code terms) to a button on a worksheet? I only know how to refer to a button that is part of a form. Once I can refer to it, how can I delete it?

This is what I have so far. It doesn’t generate any errors…but it doesn’t remove the button

Please Login or Register  to view this content.

I've also tried the following without any result:

Please Login or Register  to view this content.

[Marcol]

Have you tried Paste Special.....> formulas

Maybe on these lines

Please Login or Register  to view this content.

Hope this helps

[Lazhal]

Have you tried Paste Special.....> formulas

Maybe on these lines

Please Login or Register  to view this content.

Hope this helps

Well, okay, I can see how that would work, but it’s more of a way around my original question than a solution. Normally that would be okay, but let me explain. If I understand you correctly, you are suggesting by copy/pasting the cell data in the sheet instead of the entire sheet (like I am currently doing), I can forgo copying the button to begin with and never have to deal with the problem of deleting it.

My copy/paste method:

Please Login or Register  to view this content.

I tried copying the sheets data instead of the sheet itself earlier today. The catch is the execution time of the macro. Normally my macro can run in less than one second (minus waiting for user input) or so. When I copy and paste 65536x65535 cells or how many ever there are, the runtime of the macro goes up to about 15-20 seconds. As such, I would really prefer an answer to my original question because I prefer the copy/paste method I am currently using due to execution time.

Finally no, I don’t think I can use a small range for the copy/paste to cut down on execution time, due to the merged cells (GRRRR!!) I’m having to deal with.

Thank you for the suggestion…please keep them coming.

[Marcol]

If I understand you correctly, you are suggesting by copy/pasting the cell data in the sheet instead of the entire sheet (like I am currently doing), I can forgo copying the button to begin with and never have to deal with the problem of deleting it.

Indeed, regardless of whether it is the whole sheet or not .

Well, okay, I can see how that would work

Hmmm...?

[jolivanes]

Please Login or Register  to view this content.

Would this work?

John

[Lazhal]

Please Login or Register  to view this content.

Would this work?

John

That worked perfectly. Thank you.

I’m really scratching my head as to why that worked, and the other two ways I tried deleting it in my original code did not work.

[romperstomper]

There are 2 different types of Button. Sounds like you were using the one from the Forms toolbar but your code was looking for buttons from the Control Toolbox.

[Lazhal]

There are 2 different types of Button. Sounds like you were using the one from the Forms toolbar but your code was looking for buttons from the Control Toolbox.

Why are there two different types? Which one should I be using on a worksheet?

The one I am using on my worksheet is the one I manually added to a toolbar. Hovering over it, it says “Button (Form Control) (FormControlButton)”

Thanks.

[romperstomper]

Use the Forms one whenever possible.
The ActiveX (Control Toolbox) ones give you more formatting options but they are not as stable on worksheets.

[Lazhal]

Hmm...shouldn't my (original) code have worked then?

[romperstomper]

This one:

Please Login or Register  to view this content.

should have worked provided it was not in a worksheet code module that was not NewWorksheet, or in the ThisWorkbook module of a different active workbook.

[Lazhal]

This one:

Please Login or Register  to view this content.

should have worked provided it was not in a worksheet code module that was not NewWorksheet, or in the ThisWorkbook module of a different active workbook.

"NewWorkSheet" was created by the macro I was running, thus had no code associated with it. So I guess that's the kicker.

Seems rather strange to me, that it would work that way.

[romperstomper]

That doesn't really relate to what I said. The issue is where that code is located.