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. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,169
    Thanks
    14
    Thanked 321 Times in 315 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

  4. #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

  5. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 15 Times in 15 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.

  6. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,169
    Thanks
    14
    Thanked 321 Times in 315 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

  7. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,169
    Thanks
    14
    Thanked 321 Times in 315 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

  8. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 15 Times in 15 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!

  9. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,169
    Thanks
    14
    Thanked 321 Times in 315 Posts

    Re: Command Button to Print (Excel 2000)

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

    Steve

  10. #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
  •