Excel VBA: Print UserForm to Fit on a Page (2 Methods)

Get FREE Advanced Excel Exercises with Solutions!

In this article, I’ll show you how you can use Excel VBA to print a UserForm to fit on a page.


Excel VBA: Print UserForm to Fit on a Page (2 Methods)

Here we’ve got a UserForm called UserForm1 that contains,

UserForm to Print UserForm to Fit to a Page with Excel VBA

Our objective today is to print the UserForm with Excel VBA.


1. Print UserForm to Fit on a Page with PrintForm Method of Excel VBA

First of all, we’ll use the PrintForm method of VBA to print the UserForm.

⧪ Step 1:

Drag a new CommandButton from the Control box to the UserForm. Change the caption of it to Print.

Print Button to Print UserForm to Fit on Page in Excel VBA

⧪ Step 2:

Double click on the CommandButton. A Private Subprocedure will open. Enter the following code there.

⧭ VBA Code:

Private Sub CommandButton2_Click()
UserForm1.PrintForm
End Sub

⧪ Step 3:

Now run the UserForm and press the button Print. You’ll find the UserForm printed to fit the page.


2. Print UserForm as an Image with Excel VBA

Now we’ll use a Macro to print the UserForm to print as an Image.

⧪ Step 1:

Open the VBA window (Press ALT +F11) and insert a new module.

Opening VBA Window to Print UserForm to Fit to a Page with Excel VBA

⧪ Step 2:

Insert the following VBA code in the module.

⧭ VBA Code:

Sub Print_UserForm()

DoEvents
keybd_event VK_LMENU, 0, KEYEVENTF_EXTENDEDKEY, 0
keybd_event VK_SNAPSHOT, 0, KEYEVENTF_EXTENDEDKEY, 0
keybd_event VK_SNAPSHOT, 0, KEYEVENTF_EXTENDEDKEY + _
KEYEVENTF_KEYUP, 0
keybd_event VK_LMENU, 0, KEYEVENTF_EXTENDEDKEY + _
KEYEVENTF_KEYUP, 0
DoEvents

Workbooks.Add

Application.Wait Now + TimeValue("00:00:01")

ActiveSheet.PasteSpecial Format:="Bitmap", Link:=False, _
DisplayAsIcon:=False
ActiveSheet.Range("A1").Select

With ActiveSheet.PageSetup
.Orientation = xlPortrait
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

ActiveWindow.SelectedSheets.PrintOut Copies:=1
ActiveWorkbook.Close False

End Sub

VBA Code to Print UserForm to Fit to a Page with Excel VBA

⧪ Step 3:

Run the module.

This time the UserForm will be printed as an image.

⧭ Note:

You can change the orientation to xlLandscape to print the UserForm in landscape view.


Things to Remember

Here I’ve only shown how to print a UserForm in Excel VBA. If you want to know more about VBA UserForms, visit this site.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

So, these are the ways to print a UserForm with Excel VBA. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo