Print or Save Excel UserForm as PDF file during run time using VBA

[Music] hi this aing you are now watching aing TV today we would like to share how to print or save a user form during runtime let's go to the developer tab and click on wiship basic consider this user form to save this user form I'm going to write the code in this command button so let's double click it unfortunately we couldn't save the user form directly using VBA however we could make use of a simple head that is you can first screenshot the user form by using application do send keys to print screen the active window we can press on Alternate print screen and the code for the same key statement for alternate key is the percent sign for print screen use the code 1068 type within braces remember that this must be in terms of string therefore we should type within quotation marks sometime it might not have enough time for Excel to screenshot the user form before we proceed to the next action therefore we should give a short break by using do Advance after we have screenshot the user form we can now close to user form by using unload me since we have copied the user form to the clipboard this we Cann now past it in order to ensure that Excel does not change the workbook or the worksheet un wantedly let's add a blank shade so set a dami let's say WS equal to the ver Shades that I added with this worksheet we Cann now face it next I'm going to save the user form but before that I would like to define the file location by using the dami file path Define this variable as string the file path should be equal to the location that we need type within cotation box if you have no idea how to type the F path let's open the folder copy the location back to visual base paste it and with back slash before cation Mar if you want to have a specific name for this file use emphas and connect with the name I'm going to link the file name with the detail provided by the user let's say the first name that is typed in text box one and the last name type in test box to back to the coding part so I should join with me do textbox one another emphasis I wish to have a space before the last name another emphas followed by me do textbox 2 next use an emphas to connect with the file format since I need PDF so I would use dot PDF remember to type within cation marks and we can now save it to do so let's call the shade d s followed by dot print out if we have no idea how many components should we provide instead of using WS we can make use of the code that recognized by Excel that list work sheets followed by dot print out and we should be able to see the suggestions I would like to start from the first page to the first page since I only have one user form and I need only one cop Cy since I wish to print the user form or more precisely safe as PDF we should make use of the argument P2 file name col equal follow by the file name that L the fall path and now replace the worksheets with the variable WS next is to delete the work sheet that we added see this is no longer needed in order to add and delete the worksheet quietly we should first set the display alert using the application. display a list to force so that the user wouldn't know we add and delete a temporary worksheet now let's delete the worksheet after the worksheet is deleted let's set the display alert back to through if you wish to let user know the form is saved successfully we can make use of a message box followed by the message remember to type within codition Mouse if you wish to show together with Excel icon placee a comma and follow by the icon for example WEP information we should bear in mind that that is a the advantage of using S key statement that is the number log will be turn off to turn it on let's make use of application dot send keys and follow by the number lock key remember type within cation marks and the Cod for number lock is simply num log type within braces as well and the coding part is now done let's back to the spreadsheet I will call the user form by pressing this command button let's fill up all the par and I will save this user form by pressing this command button and the form should be save successfully let's open the F to see the result the form is saved however as we should be able to notice here part of the user form is missing to overcome this issue let's revise the code before we revise the code I'm going to check how many columns to form covers so let's call the user form alternate print screen close this user form select cell A1 basic in order to ensure that we can fit this user form to one page I will first select the columns that are fully covered by this form and now adjust in order to ensure that the total width of all this columns are exactly the same as the user form which means that for each column I should have a width of 8.57 we can undo so that it won't affect the spreadsheet now let's back to wish should basic double click on the command button to edit the code after pacing the picture of the user form we cannot adjust the columns WID use the dami Ws followed by The Columns be specific that L it should coverers column A up to column I type within cation marks and we should change the column width which is equal to 8.57 in next is to change the page setup use the WID statement together with the variable WS followed by The Dot Page set up with this properties I'm going to first change the orientation since the form is better to be presented in landscape so I would use XL landscape next is to adjust the people size I would prefer to use A5 people next is to set the print areial which is equal to the columns from a up to I lastly is to fit it since I want to fit all the columns into one pit so I should use fit to PES white if I wish to fit all the rows into one page then I should use f two pages tall set equal to one send one page start with WID of course end with width and the cing part is now done let's back to spreadsheet call the form again fill up all the particulars and now save this as PDF the form is saved successfully and now let's open and see the result as we can see no more missing part now let's write the Cod to print this form back to excel we should basic this time let's double click on the print button without writing the code again we can actually copy whatever we have type earlier copy and paste it here what we have to modify is since we are going to print the form as hard copy therefore we do not need to save it that is the F location is not needed let's delete it if you wish to delete the variable F puff of course can and the coding part to print the form is now done let's back to the spreadsheet to see the result call the user form fill up all the particulars and this time I would print it and the form is now printing as we can see here okay that's all for this video thanks for watching Hope like this see you

Channel: Ah Sing Excel TV

Views: 2,780

Keywords: excel, vba

Id: UVYhlV8Cugw

Length: 10min 6sec (606 seconds)

Published: Wed Nov 22 2023