+ Reply to Thread
Results 1 to 4 of 4

Print Workbook Command Button

  1. #1
    Sprinks
    Guest

    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

  2. #2
    Patrick Molloy
    Guest

    RE: 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


  3. #3
    Sprinks
    Guest

    RE: 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


  4. #4
    Crowbar via OfficeKB.com
    Guest

    RE: 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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1