How do I create a new commandbutton in a new workbook?

[Sally]

I am using a macro workbook to run commands to create a new workbook and
other stuff. In addition, I want to create VBA codes for a new command button
with codes to "print" all worksheets in this new workbook (not my macro
workbook). My macro workbook will be closed before users can see it. Now they
will have a new workbook and they have the option to print all the worksheets
in there.

Can someone help me with this? Thank you!

[Ron de Bruin]

This example will add a button on the Sheet1 of the new blank workbook it create
But this is a emty workbook so change this to your situation

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

Sub test()
Dim Wb As Workbook
Dim Ws As Worksheet
Dim Btn As OLEObject
Set Wb = Workbooks.Add
Set Ws = Wb.Worksheets(1)

With Ws
Set Btn = .OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Left:=.Range("C3").Left, Top:=.Range("C3").Top, _
Width:=100, Height:=30)
End With
Btn.Object.Caption = "Print workbook"
Btn.Name = "YourPrintButton"
With Wb.VBProject.VBComponents(Ws.CodeName).CodeModule
.InsertLines .CreateEventProc("Click", Btn.Name) + 1, _
"Activeworkbook.printout"
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Sally" <[email protected]> wrote in message news:[email protected]...
>I am using a macro workbook to run commands to create a new workbook and
> other stuff. In addition, I want to create VBA codes for a new command button
> with codes to "print" all worksheets in this new workbook (not my macro
> workbook). My macro workbook will be closed before users can see it. Now they
> will have a new workbook and they have the option to print all the worksheets
> in there.
>
> Can someone help me with this? Thank you!

[Sally]

Thank you, Ron. That worked like a charm. Just what I needed. Best regards.

"Ron de Bruin" wrote:

> This example will add a button on the Sheet1 of the new blank workbook it create
> But this is a emty workbook so change this to your situation
>
> See also
> http://www.cpearson.com/excel/vbe.htm
>
> Sub test()
> Dim Wb As Workbook
> Dim Ws As Worksheet
> Dim Btn As OLEObject
> Set Wb = Workbooks.Add
> Set Ws = Wb.Worksheets(1)
>
> With Ws
> Set Btn = .OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
> Left:=.Range("C3").Left, Top:=.Range("C3").Top, _
> Width:=100, Height:=30)
> End With
> Btn.Object.Caption = "Print workbook"
> Btn.Name = "YourPrintButton"
> With Wb.VBProject.VBComponents(Ws.CodeName).CodeModule
> .InsertLines .CreateEventProc("Click", Btn.Name) + 1, _
> "Activeworkbook.printout"
> End With
> End Sub
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "Sally" <[email protected]> wrote in message news:[email protected]...
> >I am using a macro workbook to run commands to create a new workbook and
> > other stuff. In addition, I want to create VBA codes for a new command button
> > with codes to "print" all worksheets in this new workbook (not my macro
> > workbook). My macro workbook will be closed before users can see it. Now they
> > will have a new workbook and they have the option to print all the worksheets
> > in there.
> >
> > Can someone help me with this? Thank you!
>
>
>