Copy a Command Button to another worksheet?

[dschomburg]

I posted this in another forum as well but have not heard anything...my brain is now melted. Please help.

I have a sort of "template" spreadsheet with various command buttons on it. On another sheet there is a button that selects the template and makes a copy of it.
My problem is that the CommandButton Objects are not being copied. and if it is possible to copy the command button objects over to the new page, how would I associate the code with the new objects?????? AGGGGGGHHHHHHHH!!!!!!!!

Thanks in advance,
Dave

 

[RobBroekhuis]

I know of no easy way to do that (there may be one, just not my forte). I think it's just as easy to recreate the button on the other sheet:

MyBtn=OtherSheet.oleobjects.add("Forms.CommandButton.1&quot

and then copy the relevant properties from your existing button. You can set up a generic sub to do that.
The code is a little more tricky. I was able to do it with the following sequence:

dim s as string, line1 as long, linecount as long
with ActiveWorkbook.VBProject.VBComponents("oldbuttonsheet&quot.codemodule
Line1 = .ProcStartLine("CommandButton1_Click",0)
LineCount = .ProcCountLines("CommandButton1_Click",0)
s = .lines(Line1,LineCount)
end with
ActiveWorkbook.VBProject.VBComponents("othersheet&quot _
.CodeModule.AddFromString s

Does that help?
Rob

 

[Loomah]

Hi
Just to add a little bit.....
There is a difference between buttons created from the 'Controls Toolbox' and the 'Forms' toolbar. Dunno the details but the bottom line is that if you create your button(s) using the Forms toolbar they WILL copy and retain the associated code.

Even if the button is copied to a new workbook the button remains associated with the original code. However the key word there is original. It doesn't copy the code but runs it from the original location so you would have to look into copying code modules between workbooks if that is your target. I've never done it myself but there are some bits of help here

http://www.cpearson.com/excel/vbe.htm

Another way is to copy the whole sheet. This SHOULD also copy buttons created using the Control Toolbox. Again this will copy to another book but will refer to code in the original. Use

ActiveSheet.Copy after:=Workbooks("book2").Sheets(Workbooks("book2").Sheets.Count)

or

ActiveSheet.Copy after:=Sheets(Sheets.Count)

as examples.

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[dschomburg]

Loomah,

Thanks a heap !

I found that it doesn't matter where the control comes from. But you do have ton's more freedom with the with the VB toolbar object. What really makes the difference is when I used "ActiveSheet.copy" instead of "ActiveCells.copy" - for some reason that just made all of the difference in the world

Thanks again,
Dave