Print Workbook Command Button
 

We estimate construction projects for architectural clients, submitting them
as an Excel workbook. Frequently our clients have few Excel skills and don't
realize that the estimate detail is frequently broken up by project area or
trade on separate worksheets, so they are confused why we've provided a
summary only and no detail.

I'd like to attach the following macro, which works, to a command button to
simplify the print process for them. Of course, I don't want to print an
image of the button, but would like it visible at all times.

Can anyone tell me how to do this?

Thanks, Sprinks

Public Sub PrintWorkbook()
On Error GoTo ErrorHandler
Dim wrksht As Worksheet
Dim cursht As Worksheet

Set cursht = ActiveSheet
Application.ScreenUpdating = False
For Each wrksht In ActiveWorkbook.Worksheets
If wrksht.Visible = xlSheetVisible Then
ActiveWindow.View = xlNormalView
wrksht.Activate
wrksht.PrintOut
End If
Next wrksht
cursht.Activate

SubExit:
Application.ScreenUpdating = True
Exit Sub

ErrorHandler:
MsgBox "There has been the following error. Please contact the macro
administrator." & _
vbCrLf & "Error Code: " & Err.Number & " " & Err.Description
GoTo SubExit
End Sub

Print Workbook Command Button
 

if its a button from the forms menu. right click on it, select Format
Control, and in the Format Comtrol form click the Properties tab. there's a
check box that needs to be unchecked there

"Sprinks" wrote:

We estimate construction projects for architectural clients, submitting them
as an Excel workbook. Frequently our clients have few Excel skills and don't
realize that the estimate detail is frequently broken up by project area or
trade on separate worksheets, so they are confused why we've provided a
summary only and no detail.

I'd like to attach the following macro, which works, to a command button to
simplify the print process for them. Of course, I don't want to print an
image of the button, but would like it visible at all times.

Can anyone tell me how to do this?

Thanks, Sprinks

Public Sub PrintWorkbook()
On Error GoTo ErrorHandler
Dim wrksht As Worksheet
Dim cursht As Worksheet

Set cursht = ActiveSheet
Application.ScreenUpdating = False
For Each wrksht In ActiveWorkbook.Worksheets
If wrksht.Visible = xlSheetVisible Then
ActiveWindow.View = xlNormalView
wrksht.Activate
wrksht.PrintOut
End If
Next wrksht
cursht.Activate

SubExit:
Application.ScreenUpdating = True
Exit Sub

ErrorHandler:
MsgBox "There has been the following error. Please contact the macro
administrator." & _
vbCrLf & "Error Code: " & Err.Number & " " & Err.Description
GoTo SubExit
End Sub

Print Workbook Command Button
 

Patrick,

As of now, I don't have a button. I'd like to place one on the body of the
worksheet for them.

Sprinks


"Patrick Molloy" wrote:

if its a button from the forms menu. right click on it, select Format
Control, and in the Format Comtrol form click the Properties tab. there's a
check box that needs to be unchecked there

"Sprinks" wrote:

We estimate construction projects for architectural clients, submitting them
as an Excel workbook. Frequently our clients have few Excel skills and don't
realize that the estimate detail is frequently broken up by project area or
trade on separate worksheets, so they are confused why we've provided a
summary only and no detail.

I'd like to attach the following macro, which works, to a command button to
simplify the print process for them. Of course, I don't want to print an
image of the button, but would like it visible at all times.

Can anyone tell me how to do this?

Thanks, Sprinks

Public Sub PrintWorkbook()
On Error GoTo ErrorHandler
Dim wrksht As Worksheet
Dim cursht As Worksheet

Set cursht = ActiveSheet
Application.ScreenUpdating = False
For Each wrksht In ActiveWorkbook.Worksheets
If wrksht.Visible = xlSheetVisible Then
ActiveWindow.View = xlNormalView
wrksht.Activate
wrksht.PrintOut
End If
Next wrksht
cursht.Activate

SubExit:
Application.ScreenUpdating = True
Exit Sub

ErrorHandler:
MsgBox "There has been the following error. Please contact the macro
administrator." & _
vbCrLf & "Error Code: " & Err.Number & " " & Err.Description
GoTo SubExit
End Sub

Print Workbook Command Button
 

Insert this into the sheet that contains the print button, this will hide it
from printing
You might have to rename commandbutton1()

Sub auto_open()

With CommandButton1()

.PrintObject = False

End With

End Sub

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200601/1