How to Make a Macro to Print Preview

8
12292
keyboard shortcut - print preview - vba to view print
Excel Spreadsheets

There are a few simple ways you can see exactly what you want to print on the Excel.

VBA Course in Excel

Let's see the 3 main ways to print a spreadsheet in Excel:

Excel Spreadsheets

CTRL + P keyboard shortcut

It's as simple as it sounds, just type CTRL + P which will automatically pop up the print page with all the details, settings and button to print

keyboard shortcut - print preview - ctrl p

File menu

Although not as fast as the keyboard shortcut, the file menu is also a very simple path. Just choose the File tab in the upper left corner of your worksheet (on the Home tab side).

keyboard shortcut - print preview - file tab

After that, a new page will open automatically where you can choose the Print option, as shown in the image below:

keyboard shortcut - print preview - feature print

Quick Access Toolbar

A third option is to use the Quick Access toolbar. First you need to enable the print button, which is the fourth icon in the image below (sheet with a magnifying glass). To do this, simply click on the little icon down the right corner of the toolbar and select the print option.

keyboard shortcut - print preview - quick access toolbar

VBA Course in Excel

Preview Printing with VBA

Now, if you are using or developing a spreadsheet that will be shared by more people, you may find people who are not very accustomed to Excel and have difficulty finding print functionality. In such cases it is possible to bind a VBA code that has this functionality to a button. Let's see a very simple step-by-step how to do this:

  • Open the VBE

Just go to the Developer tab and choose the Visual Basic option:

keyboard shortcut - visual impression - developer guide visual basic

That way a new window (Visual Basic Editor) will open.

  • Create a new module in VBE

Normally you already have 3 modules ready and empty to use, but if you have not, just right click on the Project - VBA Project window and add one.

keyboard shortcut - print preview - vba module

  • Type a new macro in the module

With the module open, go into the code window (larger blank) and start writing your first macro. To do this just type sub, give a space and enter the name that your macro will have. In our case I chose the name "print", press ENTER and you're done. It will be more or less what you will see in the code window:

keyboard shortcut - print preview - create macro in vba

VBA Course in Excel

  • Add this VBA code to print preview

Now let's write the code for this print preview feature. I will not go into the details of the code, just copy and paste this line of code:

Application.Dialogs (xlDialogPrintPreview) .Show

keyboard shortcut - print preview - vba to view print

See I wrote a comment (in green) in the code. In practice it has no functionality, just to inform another reader of what I intended to write the code.

  • Create a button

Now let's get into the details of how to use this VBA code in practice. For this we will add a form in our spreadsheet. See I made a sales data table of branches and created a rectangular button (insert tab> form)

keyboard shortcut - print preview - print preview button

  • Grip macro to button

With the button created, just select it with the right button and choose the "assign macro" option. Choose the option of the macro print.

keyboard shortcut - print preview - assign macro to shape

  • Ready! You can already use

Now, if you click the button, a new print preview window will automatically open, just like the one shown in the image below:

keyboard shortcut - print preview - print preview using macro

Obviously I've done the print set up to be neat. If you need, remember to 5 steps to print spreadsheets in Excel in this post.

VBA Course in Excel

Excel Spreadsheets

8 COMMENTS

  1. Good evening,

    Very good article, however it is possible to create a macro that gives to access the box to insert a symbol?
    Then in this box can you choose the symbol or symbols?

    Thank you

  2. macro to save in pdf with validation and folder selection

  3. macro to save in pdf with validation and folder selection

    I made a plan1 that has a selection box and I need that according to the selected report, example: "ORDERS", the macro searches the plan2 that also has a same selection box, where the reports are generated: "ORDERS" , "FINANCIAL", SALES, etc ... How do I make a macro in plan1 that regardless of the option selected in plan2, save the selected file in PDF and measures the option to choose which folder of the micro will be saved. I have very basic knowledge of VBA, and would need step-by-step, can anyone help me? I am attaching the images from the two worksheets.
    Do I need the button to save or print, next to the check box do you have to do?

LEAVE AN ANSWER

Please, write your comment
Please enter your name