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!

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

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