Solved I want to print an Excel spreadsheet using a print button.

August 3, 2012 at 04:18:43
Specs: Windows 7
I have two worksheets in my workbook. I want to create a "Print" button on Worksheet 1 (the worksheet that I use to enter data) that will print Worksheet 2.

I know how to create a print "button" on my Worksheet 1, but I have no idea how to write the code to print Worksheet 2.

I do not want the computer screen to change from Worksheet 1 upon execution of the print command.

Also, I would want to know how to enable the macro.

If there is someone who can help a guy who has no knowledge of VB programming, I'd appreciate it. Thank you for any help you can be.


See More: I want to print an Excel spreadsheet using a print button.

Report •

#1
August 3, 2012 at 04:32:46
you can do something like

ThisWorkbook.Worksheets("sheet2").PrintOut

change "sheet2" to whatever the name of the sheet you want to print


Report •

#2
August 3, 2012 at 05:57:08
Thank you for your help, but I don't even know how to write the code.

When I create the Print Button, it is labeled Button31_click. Should I click on "New" or "Record"? Or just click "Ok". Can you give me all the lines I should type?

Also, I can't figure out how to enable a macro. It keeps telling me I need to enable macros. I click on "Macro Security", click on "Enable all macros" and it still doesn't work.

Thanks so much fo your patience.


Report •

#3
August 3, 2012 at 06:43:12
✔ Best Answer
Hi,

ok firstly to enable macros here is what you need to do,

1) enable the Developer tab - to do this click on file
2) click on options
3) click on customize ribbon
4) tick the little box on the right that says 'developer' then click ok

now at the top of your worksheet you will have a new tab called developer?

1) click the developer tab
2) click the yello triangle which says 'Macro security'
3) now you can select one of two options
a) disable all macro with notification
b) enable all macros (not recommended......)

i have mine set to enable all macros (not recommended........) however you can selec the other if you wish, but be warned that if you ever get any dodgy macros from the web or someone else it could harm you machine.

close excel and reopen it. if you chose the first option it will now show you a yellow bar going across the top of excel, with a button called enable content, click this and macros will be enabled. however if you chose the second option macros will be abled automatically.

Now to add the button

1) click the developer tab
2) click insert, now under the ActivX group select the command button andplace it on your workheet

3) now double click this button and you will be taken to the VBA consle, you will see the code

Private Sub CommandButton1_Click()

ThisWorkbook.Worksheets("sheet2").PrintOut

End Sub

now go back to your worksheet, still under the developer tab, you will see a button called 'Design Mode' click on it, this will enable your button. click the button and see what happens.

what you will need to do is, paste the code i gave you above between this code, like this

Private Sub CommandButton1_Click()

End Sub


Report •

Related Solutions

#4
August 3, 2012 at 06:50:44
@ AWTL:

Great explanation, but I think you have a couple of steps reversed.

Unless I'm mistaken (could be!) you appear to have your full code listed before the code outline that will appear when the OP opens the VBE.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
August 3, 2012 at 07:03:03
Hi DerbyDad03,

thanks i think im my haste i put the code in the wrong place, thanks for the correction, great spot...!

@ Mark1011

when you double click the button for the first time, you will automatically taken to the VB editor and you will see the code:

Private Sub CommandButton1_Click()

End Sub


between these lines, paste the code i gave you earlier so that your code looks like this

Private Sub CommandButton1_Click()

ThisWorkbook.Worksheets("sheet2").PrintOut

End Sub

now you can continue to follow the above.


Report •

#6
August 3, 2012 at 07:25:40
Dear AlwaysWillingToLearn,

Wow! It worked!! This is sooooo cool!

Thanks so much for your help!

Only one other question...if it's a big deal, don't worry about it...I would like to hide the worksheet that I print. Is there a way to print a hidden worksheet?

Thanks again soooo much! I can't believe! Really appreciate it.

Yours,

Mark


Report •

#7
August 3, 2012 at 07:59:34
Hi,

glad it worked!!

The only way that i know of printing a hidden worksheet is to unhide it, print, then hide it again. like this..

assuming sheet2 is already hidden, this is the code.

Private Sub CommandButton1_Click()

Sheets("sheet2").Visible = True

ThisWorkbook.Worksheets("sheet2").PrintOut

Sheets("sheet2").Visible = False

End Sub


Report •

Ask Question