UserForms, also called dialogue boxes, are graphical windows that allow users to enter data into fields and insert that data into Excel spreadsheets. A UserForm is embedded in an Excel workbook and runs when a user opens the Excel file, typically via a macro. Because UserForms take advantage of the Visual Basic language, they can be generously customised to ensure that data entered into their fields is complete and valid prior to being transferred to the Excel worksheet itself for further manipulation.
- Skill level:
Things you need
- Microsoft Excel with Visual Basic Editor
Open Microsoft Excel and create a new blank workbook by clicking on the File menu and selecting "New." Save the Excel file as "update_worksheet.xls."
Keep the workbook open and open the Visual Basic Editor by hitting Alt-F11. Create a new UserForm by clicking on the Insert menu and selecting "UserForm." Name the UserForm "transferForm" by changing the Name attribute in the Properties menu. Change the Caption attribute to "transferForm."
Add a textbox to the form by selecting a textbox from the toolbox and dragging it into place on the form. Name the textbox "transferInput" in the properties window. If the toolbox is not open, select the View menu and Toolbox.
Add an update button to the form by selecting a CommandButton from the toolbox and dragging it into place on the form. Name the CommandButton "transferButton" in the properties window and change the caption attribute to "Update Worksheet."
Add a close button to the form by selecting a second CommandButton from the toolbox and dragging it into place on the form. Name the CommandButton "closeButton" in the properties window and change the caption attribute to "Close Form."
Open the Visual Basic code editor by double-clicking on the transfer Button (update button). Add code to the transfer Button _click() function that transfers any data entered into the textbox to the update_worksheet.xls worksheet when the user clicks the button. Close the Visual Basic code editor.
Private Sub transferButton_Click()
Dim transfer Worksheet as Worksheet
Set transfer Worksheet = Worksheets("Sheet1")
transferWorksheet.Cells(1,1).Value = Me.transferInput.Value
Reopen the Visual Basic code editor by double-clicking on the close Button (close button). Add code to the closeButton_Click() function that closes the form when the user clicks the button. Close the Visual Basic code editor.
Private Sub closeButton_Click()
Run the form's code by clicking on the Run menu and selecting "Run Sub/UserForm." Enter data into the input field and click the "transferButton." Ensure that the data is successfully transferred to the first cell value in the Excel worksheet, switching to Excel if necessary. Save and close the running UserForm.
Use an Excel event procedure to open the UserForm when a user opens the workbook in Excel. Open the Project Explorer and double-click the ThisWorkbook code window. Enter a macro that opens the form when the worksheet opens and save the macro. Save and close Visual Basic and update_worksheet.xls.
Private Sub Workbook_Open()
Reopen "update_worksheet.xls." The UserForm transfer Form will open. Type some text into the "transferInput" field and click the "transferButton." The text entered into the transfer Form UserForm will be transferred to the first cell in Sheet1 in update_worksheet.xls.
Tips and warnings
- To open the Properties Window or the Project Explorer, select these items from the View menu in the Visual Basic editor.
- To resize controls added to the UserForm, use the square handles that appear when a control is selected.
- Try adding validation to the UserForm's code to ensure that users are entering valid data.
- Macros will not run in all versions of Excel.
- Certain Excel security settings will not allow macros to run.
- Support for the Visual Basic Editor was dropped in Excel 2008 for Mac.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for