Create an Excel VBA Data Entry Form and Export a PDF Report
Question details
Create a VBA data-entry form that fills an Excel report and exports it as a PDF.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user wants to enter report data through a single form rather than editing worksheet cells directly.
- Observed behavior
- The completed form should update the report worksheet and produce a PDF using its configured print layout.
Use a desktop version of Excel that supports VBA, and save the workbook as an Excel Macro-Enabled Workbook (.xlsm) in a writable folder. Enable the Developer tab in Excel settings if it is not visible, and enable macros only if you trust the workbook.
Build a VBA UserForm and export the report sheet
Use this approach to collect values in a form, place them on a report sheet, and create a PDF with one button.
This example writes a name and an amount to cells B2 and B3 on a sheet named Report. Adjust the controls and cell references to match your report. The UserForm opens in front of Excel; Excel itself remains open.
Create or rename a worksheet to Report. Add labels for the name and amount next to cells B2 and B3, then select the report range and choose Page Layout > Print Area > Set Print Area. Use Page Layout to adjust orientation, margins, and scaling.
Open Developer > Visual Basic. In the VBA editor, choose Insert > UserForm and set its (Name) property to DataEntryForm. Add two TextBox controls named txtName and txtAmount, and a CommandButton named cmdExport. Add labels describing the fields and set the button caption to Export PDF.
Double-click the Export PDF button and add this code to the form's code window: Private Sub cmdExport_Click() If Trim(Me.txtName.Value) = "" Or Not IsNumeric(Me.txtAmount.Value) Then MsgBox "Enter a name and a numeric amount." Exit Sub End If If Len(ThisWorkbook.Path) = 0 Then MsgBox "Save the workbook in a writable folder first." Exit Sub End If On Error GoTo ExportError With ThisWorkbook.Worksheets("Report") .Range("B2").Value = Me.txtName.Value .Range("B3").Value = CDbl(Me.txtAmount.Value) .ExportAsFixedFormat Type:=xlTypePDF, _ Filename:=ThisWorkbook.Path & Application.PathSeparator & "Report.pdf", _ Quality:=xlQualityStandard, _ IgnorePrintAreas:=False, _ OpenAfterPublish:=True End With Exit Sub ExportError: MsgBox "The PDF could not be created: " & Err.Description End Sub
In the VBA editor, choose Insert > Module and enter: Public Sub ShowDataEntry() DataEntryForm.Show End Sub
Save the workbook, return to Excel, and choose Developer > Macros > ShowDataEntry > Run. Enter a name and numeric amount, then select Export PDF. Check that Report.pdf opens and displays the expected layout.

Create and export spreadsheet reports with WPS Office
WPS Office is a free, lightweight alternative with a familiar interface for opening existing spreadsheets, editing report data, and exporting reports to PDF. It supports common Excel formats for an easier move between office suites; test VBA UserForms and macros separately before relying on them.

Frequently Asked Questions
Why does the form export a blank or incomplete PDF?
Check that the form writes to the correct cells on the Report sheet. Select the report range and choose Page Layout > Print Area > Set Print Area, then review the result in File > Print before exporting again.
Where is the PDF saved?
The example saves Report.pdf in the workbook's folder using ThisWorkbook.Path. Save the workbook in a writable local folder before running the macro.
How can I open the data-entry form?
After adding the ShowDataEntry macro to a standard module, choose Developer > Macros, select ShowDataEntry, and click Run.
Does the UserForm run without Excel being open?
No. A VBA UserForm runs within desktop Excel. The example displays the form in front of Excel but does not turn the workbook into a standalone application.




