MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Alt+F11? Print-area macro

Posted by espen on January 09, 2001 6:12 AM

To make a kind of macro to print a given area,
I was told to push Alt+F11, and then go to Insert>Name>Define.

But there was no options for "name" when I pushed Alt+F11? What have I done wrong?

Posted by Greg on January 09, 2001 1:53 PM

The insert>Name>Define is on the spreadsheet itself. That is where you name a range. The Alt+Fll brings you to the visiual basic editor screen where you can write a macro. I hope that helps clarify. If you need anymore info just post a response.

Posted by espen on January 09, 2001 5:58 PM

Wow, it is almost working! I went to view>name>define before I pushed Alt+F11, and I think it worked.

But, when I clicked the button, I got up a preview. Is it difficult to make a formula which drop the preview, and print right away, or maybe it is smart to have a preview, just in case? Do you have any thoughts about it?

Hey, this is starting to be fun.


Posted by Greg on January 09, 2001 8:09 PM

There should be a line in the line of code that calls the print commmand. In that line there there should be "Preview: True" ,if you change that to false it will not do the preview. I am assuming you did a macro. As for the preview option, it depends on what you are doing. It is usually never a bad thing to see what you are printing.

Posted by espen on January 09, 2001 10:24 PM

Thank you Greg

One last question on this subject

Can I copy the text, paste it, and change the areas? Or do I have to write it everytime because of the recording?


Posted by Dave Hawley on January 09, 2001 11:36 PM

Hi espen

The answer is yes but you will need to change the name after each "Sub" as well as the range.

I think it may have been me that gave you the code, I'm sorry about the typos. Here is the corrected version.

1. Push Alt+F11 and go to Insert>Module and paste in this code as many times as you have ranges to print: e.g if you have 10 ranges then paste it in 10 times, one below the other.

Sub PrintRange()
With ActiveSheet
.PageSetup.PrintArea = ""
.PageSetup.PrintArea = "$A$1:$D$9"
'Replace the line below with .PrintOut to just print
.PageSetup.PrintArea = ""
.DisplayPageBreaks = False
End With
End Sub

2. Change all PrintArea ranges to suit. But leave the [.PageSetup.PrintArea = ""] as is.

3. Give the Procedures DIFFERENT one word names. E.g change "MyPrintRange" to MyPrintRange1 etc.

4. Push Alt+Q and then go to View>Toolbars>Forms. Double Click the "Button" and paste in the cell you want it. Click the Macro name to assign to this button and then OK.

5. Click the next cell where you want a Button and again assign the macro. When you have finished push Esc to remove the button from your cipboard.

6.Right click on each button and select "Format Control". and make any changes.

7. Save.

If anything is still not clear, just yell!!


OzGrid Business Applications

Posted by Robert Faass on January 13, 2001 4:34 AM

If anything is still not clear, just yell!! Dave

For this kind of stuff: did you look at custom views? You set up a view, including a certain (set of) print area's and add the view. Later you can use the views to print the different print area's. Maintenance is also easy: just change a certain view... just my two cents...