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

[Martin Lloyd]

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?

 

[Luke M]

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:

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.

 

[Martin Lloyd]

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.

 

[Martin Lloyd]

Thank you, it works a treat!

 

[Luke M]

Glad it worked, and glad you could join us Martin. Come back anytime.

 

[Martin Lloyd]

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!

 

[Luke M]

Hi Martin,

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

 

[Martin Lloyd]

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.

 

[Luke M]

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:

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

 

[Martin Lloyd]

Thank you so much for your time on this, it works perfectly.

 

[Lis]

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!

 

[Luke M]

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.

 

[ahargrave]

Hello everyone I need help. I'm looking for a type of VBA code that could only print where there are active data in the cell blocks in excel where it would not have to print out all the other pages that has no data in it but only print where there is data in the excel worksheet on sheet1 any other worksheets like sheet2 and so on will not be printed only if I request the VBA code to print Sheet2 and so on in the worksheet.

So if you could help me I will be very pleased that you did.
Thanks!

Below is a file that I want to send you so that you can figure out what to do on the print button vba code area. and as for the submit button I need a code for that to start A17, B17, H17 and J17. So that when I input the data in the textbox and then click on submit data it will put the data in the cell blocks listed then goes down to the next line over to A again to line 18 and so on.

The name of the file is listed below as:
Bank of Home Savings.xlsb

Thank You!

 

[vletm]

ahargrave
As You're a new member, please reread Forum Rules

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

• Start a new post every time you ask a question, even if the theme is similar. The original author may continue asking questions whilst the post is in progress and of course if you are answering questions you may need to ask questions of the initial poster.

As also written over three years ago (in #12 reply).