Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: Print only certain worksheets?--Excel experts pls help

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Atlanta
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I want to make a popup or some type of inquiry menu where it asks the user to specify which worksheets in the workbook to print. I have a data entry page, and four printable worksheets. Sometimes I only want to print two of the worksheets, but not the data page or the other two. Any way to do this besides printing the entire workbook or one worksheet at a time? It would be ideal if a popup menu came up with checkboxes for all of the available worksheets and the user could check off which ones to print.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Wellington
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    If you wanted to print more than one sheets, there are alternative ways to do that.

    First, select one of the sheets you wanted to print, then press & hold down on "Ctrl" key, and then select the other sheets you wanted to print. After all the sheets you wanted to print are highlighted, then press print, it should print all the highlighted sheets at one go.

    Otherwise, if you really want a checkbox / listbox menu, you can set that up, and then add the following line of code after the checkboxes are ticked (similiar coding):

    Sheets(Array("sheet1", "sheet2")).Print


  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    Atlanta
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the start. However, I know very little about VB. I know how to make Macros and assign them to buttons, but as far as lists and checkboxes, I know nothing. Could you instruct me a little further on how I could set up such a box that contains the ability to check off each sheet that I want printed? If not, is there a website the teaches the basics of these boxes in Excel? Thanks!

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Wellington
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    Hope the following steps might get you started:

    STEP1: Select Viscal Basic Editor under tools/macros, that will bring you to the code editor.
    STEP2: Select Insert/userforms, that will insert an userform for you in this workbook.
    STEP3: You might see there is a toolbox popup menu after you have inserted the userform, choose checkbox, and click on the userform, that would insert a checkbox on your form. don't have to change the name of the checkboxes yet, insert 4 of them
    STEP4: Double click on the userform (not on the checkboxes), that will bring you to the coding behind this userform.
    STEP5: at the top of the coding page, there are 2 options, select "userform1" in the left option, and select "initialise" for the right. That will setup a private sub for the initialisation of this userform.
    STEP6: Insert the following codes(similiar codes only) into the private sub setup in the SETP5:

    CheckBox1.Caption = Sheets("Sheet1").Name
    CheckBox2.Caption = Sheets("Sheet2").Name
    CheckBox3.Caption = Sheets("Sheet3").Name
    CheckBox4.Caption = Sheets("Sheet4").Name

    STEP7: Go back to userform, and insert a button, and double click on the button, and then insert the following codes:
    If CheckBox1.Value = True Then Sheets("Sheet1").PrintOut Copies:=1
    If CheckBox2.Value = True Then Sheets("Sheet2").PrintOut Copies:=1
    If CheckBox3.Value = True Then Sheets("Sheet3").PrintOut Copies:=1
    If CheckBox4.Value = True Then Sheets("Sheet4").PrintOut Copies:=1
    Unload UserForm1

    STEP8: setup your macro button on your sheet to have the following line, just to activate you userform:

    userform1.show

    Hope the above helps.

  5. #5
    New Member
    Join Date
    Mar 2002
    Location
    Atlanta
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You are a lifesaver. Thank you so much for this. The formula works perfectly, and I know how to customize it for my particular application.

    I am very interested in learning to write and be efficient in VB. I work for a major brokerage firm that has terrible software, so I am left creating my own. Any good suggestions on starter books/websites?

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I found step by step Excel VB my microsoft a good starting point, it shows you how to use VB plus there is a bit there about customizing applications and user forms etc.

    I am in a similar situation to yourself, our department has complicated spreadsheets which are in a mess simply as the people who set them up were clueless about excel and I am currently transforming them to be useful and flexible and more powerful etc. Hard work but bags of fun I must admit.

  7. #7
    New Member
    Join Date
    Feb 2006
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This works like a champ! I have one question however. Is there a way to ammend this script allow for selection of installed printers? Currently this just sends to the default printer.

  8. #8
    New Member
    Join Date
    Mar 2006
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    This seems exactly what i am looking for.

    Is it possible to specify printing more than 1 worksheet?

    i.e

    If CheckBox1.Value = True Then Sheets("Sheet1, Sheet9, Sheet13").PrintOut Copies:=1

    thanks

  9. #9
    New Member
    Join Date
    Mar 2006
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default AWESOME!

    Thanks so much! This works fantastic! Your detailed step-by-step instructions were very easy to follow and simple to do. This will come in very useful!

  10. #10
    Board Regular
    Join Date
    Aug 2002
    Location
    Nashville, TN USA
    Posts
    188
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I searched for messages on printing select worksheets and found this one, which is exactly what I am trying to do. I have followed the steps above, and it isn't working. I am not so good with VBA, and have probably screwed up something simple.

    I am pretty sure I made it through the first 6 steps. I replaced "Sheet1" etc with my actual sheet names. And, I replaced the word "Name" with what I wanted that option to be called - is this correct? I copied the text from step 7 verbatim, and only changed "Sheet1" etc with my sheet names. I think I have screwed up step 8...I created a new macro (see below), created a button and assigned this macro to the button. Nothin is happening.

    Sub Print_Sheets()
    '
    ' Print_Sheets Macro
    ' Macro recorded 4/4/2006 by Sarah C. Rosenberg
    ' UserForm1.Show
    End Sub

    Thoughts? Suggestions??

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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