1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Creating a print command button and VBA code to print selected cells

Discussion in 'VBA Macros' started by Martin Lloyd, Aug 13, 2014.

  1. Martin Lloyd

    Martin Lloyd New Member

    Messages:
    12
    I am using excel 2010 and I am a raw beginner in the use of VBA.
    I would like to have a command button on a worksheet that, when clicked, automatically prints a selected area of the worksheet e.g. (a2 to g24).
    I have eight sections in this worksheet requiring a command button so wish to assign a button to each section to print out the relevant cells.
    Can anyone please help me, step by step, to do this?
  2. Luke M

    Luke M Excel Ninja

    Messages:
    9,277
    Right-click on a worksheet tab, view code. This will open the Visual Basic Editor (VBE). On the Menu bar, go to Insert - Module. This is where we will enter our code. A macro to print a specific range of cells looks like:
    Code (vb):

    Sub PrintSomeCells()
    Range("A2:G24").PrintOut
    End Sub
    You mentioned that you have different sections, so you would want to create different macros (with different names and ranges) for each. Close the VBE. Now, in your worksheet, insert any object (a shape, such as a circle/rectangle, or a command button from the forms menu). You should get the option to assign a macro the the object. If not, right-click on the object, and select "Assign macro". Choose the correct macro, and you are set.
    Thomas Kuriakose likes this.
  3. Martin Lloyd

    Martin Lloyd New Member

    Messages:
    12
    I only joined this forum today, (the only one I have ever joined), so I didn't expect such a quick response! So thank you very much for your help. I will give it a try and report back.
  4. Martin Lloyd

    Martin Lloyd New Member

    Messages:
    12
    Thank you, it works a treat!
  5. Luke M

    Luke M Excel Ninja

    Messages:
    9,277
    Glad it worked, and glad you could join us Martin. Come back anytime. :)
  6. Martin Lloyd

    Martin Lloyd New Member

    Messages:
    12
    I haven't used this website for some time and, believe it or not, I can't seem to find where to post a question! Please could you help on this. This must be the dummest question you have had to date!
  7. Luke M

    Luke M Excel Ninja

    Messages:
    9,277
    Hi Martin,

    After going to any of the forums, in top tight, you'll see the "Post New Thread" button. :)

    Attached Files:

  8. Martin Lloyd

    Martin Lloyd New Member

    Messages:
    12
    I have another problem I am trying to resolve.
    In a workbook with six worksheets, I am trying to get it to always open in sheet1 regardless of which sheet I am in when I exit.
    I have highlighted the 'workbook' in VBE and on the right side boxes put in 'workbook' in the left box with 'open' in the right box, then

    Private Sub Workbook_Open()
    Worksheet("sheet1").Range("a1").Select
    End Sub

    If I am in sheet 2 and save work and exit, it returns to sheet 2 next time I open the programme so obviously I have got something wrong.
    Should I write the code in both workbook and sheet 1? Your help will be much appreciated, thank you.
  9. Luke M

    Luke M Excel Ninja

    Messages:
    9,277
    The code you posted should be in the 'ThisWorkbook' module.
    However, you need to tell it to specifically go to the sheet. In the current form, it is the computer is somewhat naive in that it's trying to select A1 w/o first checking to make sure it's on the right sheet. Try this way:
    Code (vb):

    Private Sub Workbook_Open()
    Application.Goto (Worksheets("Sheet1").Range("A1"))
    End Sub
  10. Martin Lloyd

    Martin Lloyd New Member

    Messages:
    12
    Thank you so much for your time on this, it works perfectly.
  11. Lis

    Lis New Member

    Messages:
    2
    Hi. I have a similar query to this...ie. how do I write a macro to print. Difference to Martin's question is that I do not have a set printing area since data will continue to be added to the sheet. How can I get it to set the print area based on selecting all rows with data (columns will not change)?
    Thank you!
  12. Luke M

    Luke M Excel Ninja

    Messages:
    9,277
    Hi Lis,

    I'd suggest starting a new thread, rather than continuing this one. This one is pretty old, and a new thread will gather more attention/possible answers.

Share This Page