I have a macro that makes a copy of a sheet, changes the name of the workbook and closes. When it copies it includes the 4 command buttons that are on that sheet. How can I copy the sheet and note the command buttons? I have tried changing the properties to don't move, but to no avail they CB still copy.
Thanks for the help.
Please Login or Register to view this content.
LeapingLizard
Last edited by LeapingLizard; 10-26-2012 at 10:44 AM.
I just add a command to delete them from the copied sheet. It would be easier than copying ranges, formulas, etc.
abousetta
Please consider:
Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
Is there a way to delete them after the sheet is copied?
Hi LeapingLizard,
Replace your Code with this:
Hope that helps.Please Login or Register to view this content.
Please consider:
Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .
Thanks Winon for trying, but it did not change anything. I even tried putting ( ActiveSheet.DrawingObjects.Delete) after I copied and before I saved it and that did not work.
I am open to try anything else if there are any other suggestions.
Thanks all,
LeapingLizard
Hi LeapingLizard,
In your first post you ask:
The Code I have supplied you with, works fine on my side regarding the Buttons. Do they still show when you copy the Sheet?How can I copy the sheet and not the command buttons?
If that is the case, you are most likely using Active X Command Buttons and not Form Buttons, in which case the Code will have to be adjusted.
Can you not maybe, upload a sample WorkBook for us to have a look at?
Thanks Winon,
I have solved it. Here is the code I used;Thanks,Please Login or Register to view this content.
LeapingLizard
Hello LeapingLizard,
Well, that explains everything!
There is a BIG difference between a Button and a Shape to which you assign a Macro to. You actually used a Shape of some sort with Code to appear and respond like a "Button". That is why my Code failed in your application.
With your Code the Shapes will still be there, using Memory and increased actual file size. It is just not Visible.
Maybe you would want to adapt your Code to rather Delete the Shape/s, instead of turning visibility to False.
Hi Winon,
Thanks for that feedback. Yes you are right I want to limit the file size, so what could I use to delete it. I am including a copy of the workbook.
Hello LeapingLizard,
Add the below to your Code
See Post No.4 where the other Code for deleting of Buttons appear, remove that, and use the above instead,Please Login or Register to view this content.
Thank you my friend. You are very helpful. I appreciate your knowledge.
LeapingLizard
I was certain that that would work. but it through an object error. Any ideas?
Here is where I put it and instead of an object error it gives a run-time error.
Thanks for your Help!Please Login or Register to view this content.
LeapingLizard
Sheet is Protected,
Try it like this:
Let me know how it goes.Please Login or Register to view this content.
Last edited by Winon; 10-26-2012 at 04:48 PM.
Sorry, no goes! It did not work.
LeapingLizard
What is the Password of the WorkSheet?
Help Template.xlsOops! I included an unprotected sheet. Maybe this will help.
I have removed the Password in the meantime, so check out the attached WorkBook.
Remember to first save a Back-up copy.
Last edited by Winon; 10-26-2012 at 11:41 PM. Reason: Corrected some Code
Does anyone else have an idea as to how to delete shapes when copying to eliminate using memory as this is copied daily? Thanks!
Did @Winon example not work for you? If so, then what are the problems?
@ abousetta,
Thank you for asking that question, since everything works well on my side. Did you perhaps checked the WorkBook to see if there are any problems with Shapes maybe not deleting in the newly created WorkBook?
Hi Winon,
The question in post #20 was really directed to @LeapingLizard since in post #19 (s)he is asking for new ideas. I think your solution works fine and that's why I am not sure what problems he is facing.
abousetta
Thanks to both of you. I have now solved it for good with your help. I certainly appreciate you guys hanging in there with me. I used Option Explicit and declared my variables and now it works swell. I will click on both your star Icons and add to your reputations. People helping people you got to love it.
Thanks again,
LeapingLizard
Thanks for the feedback and glad we were able to help.
Good luck.
abousetta
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks