Assigning a macro to an existing function button

**Usethaschwarz** · 05-24-2012, 05:07 PM

I have a spreadsheet that will be distributed to several people to use and fill out, but when anyone wishes to print this spreadsheet I need certain columns to be hidden and not show up on the printed version. Is it possible to assign a macro to the print icon so that when anyone prints this spreadsheet those columns will be hidden in the printed version? Hopefully this is possible as well as having those hidden columns reappear in the spreadsheet after printing.

**Mordred** · 05-24-2012, 05:25 PM

In the Workbook module you can set your print area and shouldn't actually need to hide any columns. Here is an example:

Please Login or Register  to view this content.

**Usethaschwarz** · 05-25-2012, 10:21 AM

That seems like it should work. Since I have never used VBA or macros in excel before, could anyone explain to me how to run this code? Also, my print range is from A1 to N68, excluding the cells from L31 to N48, is there a certain way to input this into the code above? My other quick question is, will this run automatically when anyone goes to print this document? Thanks for your help!

**Mordred** · 05-25-2012, 12:08 PM

Hi Usethashawarz, I see your shwartz is bigger than mine!

I don't know if this does what you want but do let me know:

Please Login or Register  to view this content.

**Usethaschwarz** · 05-25-2012, 03:18 PM

Thanks! Do you mind telling me how enter this into VBA? I keep getting a bunch of errors when trying to enter/run the code.

**Mordred** · 05-25-2012, 04:22 PM

Sure: Open your VBA IDE using either the button in Excel or alt + F11. On the left side of the IDE window, you'll see "ThisWorkbook". That's the Workbook module. Double click the Workbook module and then copy and past the code into there and then try printing.

**Usethaschwarz** · 05-29-2012, 08:50 AM

Mordred, I copied and pasted the code into "ThisWorkbook." When I try to print, the window for Sheet 3 pops up and it says there is an error. The code on sheet 3 says:
APPENDING SHEET ISSUED
Private Sub Click()
End Sub

Private Sub CheckBox1_Click()
End Sub

It looks like it automatically has that code there when I copy and paste yours. But an error window pops up saying: Compile error: Expected: End of Statement, and the word ISSUED at the top of Sheet 3 is highlighted.
Any suggestions? I was able to print, but there was no effect on the print area as the entire document printed. Thanks again for your help!

**Usethaschwarz** · 05-29-2012, 08:58 AM

Mordred, looking through the code again, it also looks like the first line of your code it highlighted with a yellow arrow to the left of it. I'm not sure if this is has anything to do with the issue.

**Usethaschwarz** · 05-29-2012, 11:36 AM

I realized its probably easier if you just take a look at the actual document. Basically I need the Cost Ea, Labor Rate and $ Change Impact columns to be hidden whenever anyone goes to print the document out.

ECR-ECN_Request_Form.xls