Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Mar 2003
    Location
    Summerville, South Carolina, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Command Button to Print (Excel 2000)

    I need some help. Being new to the world of Excel VB, I need a way to create a command button and onclick make it print all the worksheets. Any help would be greatly appreciated.

    Thanks

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts

    Re: Command Button to Print (Excel 2000)

    Add the command button from the control toolbox
    Dbl-click it and you will go to VB with this code listed:
    <pre>Private Sub CommandButton1_Click()

    End Sub</pre>


    Add this code to it:
    <pre>Private Sub CommandButton1_Click()
    Dim sht As Variant
    For Each sht In Sheets
    sht.PrintOut
    Next
    End Sub</pre>


    alt-Q to quit VB.

    Or alternately add this to a module in VB:
    <pre>Sub PrintAllSheets()
    Dim sht As Variant
    For Each sht In Sheets
    sht.PrintOut
    Next
    End Sub</pre>


    Add a command button from the FORMS toolbar
    in the assign macro dialog select
    PrintAllSheets
    from the list

    Steve

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Command Button to Print (Excel 2000)

    This macro, attached to a button will print all of the worksheets in the active workbook. Is that what you want?

    <pre>Public Sub PrintAllSheets()
    Dim oSheet As Variant
    For Each oSheet In Sheets
    oSheet.PrintOut
    Next oSheet
    End Sub
    </pre>

    Legare Coleman

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Command Button to Print (Excel 2000)

    Possibility one: Forms toolbar.
    - Select Tools | Macro | Macros... (Alt+F8)
    - In the Macro name box, enter PrintWorkbook.
    - Click Create.
    - Make the macro look like this:

    Sub PrintWorkbook()
    ActiveWorkbook.PrintOut
    End Sub

    - Switch back to Excel (Alt+F11)
    - Select View | Toolbars | Forms
    - Select the Command Button button on the toolbar.
    - Click on the worksheet, or drag a rectangle of the desired size.
    - When you release the mouse button, the Assign Macro dialog will be displayed.
    - Select the PrintWorkbook macro, then click OK.
    - You can edit the text on the button, and right-click it for further formatting.
    - Click on the worksheet outside the button.
    - Your button is now ready to be used.
    - You can hide the Forms toolbar now.

    Possibility 2: the Control Toolbox.
    - Select View | Toolbars | Control Toolbox.
    - Select the Command Button button on the toolbar.
    - Click on the worksheet, or drag a rectangle of the desired size.
    - Double click the new button to create the On Click event procedure. Make it look like this:

    Private Sub CommandButton1_Click()
    ActiveWorkbook.PrintOut
    End Sub

    - Switch back to Excel (Alt+F11)
    - Right-click the command button to edit the caption and to format it.
    - When you're done, click the Close Design Mode button on the Control Toolbox.
    - Your button is now ready to be used.
    - You can hide the Control Toolbox now.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts

    Re: Command Button to Print (Excel 2000)

    This code will give a type mismatch run time error if there are any chart sheets in workbook.

    You should use:
    <pre>Dim oSheet As Variant
    For Each oSheet In Sheets</pre>


    if you want all the sheets or use:
    <pre>Dim oSheet As Worksheet
    For Each oSheet In Worksheets</pre>


    If you want just the worksheets.

    Steve

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts

    Re: Command Button to Print (Excel 2000)

    I learn something new!

    I didn't know (at least until now <img src=/S/smile.gif border=0 alt=smile width=15 height=15>) you could print all sheets with the activeworkbook object. Much shorter code than my method of doing each sheet!

    Steve

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Command Button to Print (Excel 2000)

    I'm glad i could teach you something about Excel. A small compensation for all I have learned from you!

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts

    Re: Command Button to Print (Excel 2000)

    I can return the compliment. I have learned alot of VB programming from your code.

    Steve

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Command Button to Print (Excel 2000)

    I realized that and changed the Dim statement within a minute of posting my message. You must have read the post within seconds of when I posted it. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Legare Coleman

Posting Permissions

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