Rifat Hassan
In this article, I’ll show you how you can use Excel VBA to print a UserForm to fit on a page.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
2 Easy Methods to Print UserForm to Fit on a Page with Excel VBA
Here we’ve got a UserForm called UserForm1 that contains,
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.
⧪ 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.
Read More: How to Print Excel Sheet in A4 Size (4 Ways)
Similar Readings:
- Print Titles in Excel Is Disabled, How to Enable It?
- Print Multiple Excel Sheets to Single PDF File with VBA (6 Criteria)
- Excel Button to Print Specific Sheets (With Easy Steps)
- How to Print Horizontally in Excel (4 Methods)
- How to Print Excel Sheet with Table (4 Methods + Tricks)
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.
⧪ 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
⧪ 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.
Read More: Excel VBA: How to Set Print Area Dynamically (7 Ways)
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.
Conclusion
So, these are the ways to print a UserForm with Excel VBA. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.
