Microsoft Excel 2007 is more than rows and columns of data. Its powerful tools include formulas, functions, charts, pivot tables and filtering. In addition, Excel comes with its own programming language, Visual Basic for Applications (VBA). When you record a macro, behind the scenes, Excel translates your keystrokes into VBA code. With the Visual Basic Editor, you can edit recorded code and write your own. Use VBA to give your project a professional edge. Create a custom dialogue box, then dress it up with the picture of your choice.
- Skill level:
Other People Are Reading
Open the Visual Basic Editor. Click "Visual Basic" in the "Code" section of the "Developer" tab.
Select View > Project Explorer from the menu, then select View > Properties.
Select Insert > UserForm from the menu. You will see a blank user form and the Controls Toolbox.
Add a text box to the form. In the Controls Toolbox, hover over icons to find the text box, which looks like "ab." Click the icon. Click the user form to put the text box on the form, placing it on the right to leave room for a label, and leaving room above and below.
Add a label to the form. Find "Label" in the toolbox (it looks like a large A). Using the same method, place a label to the left of the text box.
Add two command buttons. Find "CommandButton" in the toolbox. Use the same procedure to place two command buttons side by side at the bottom of the form.
Click the label on the user form. In the Properties Window, next to "Caption," change "Label1" to "Enter Name."
Click the text box on the user form. In the Properties Window, next to "(Name)," change "TextBox1" to "Name_txt."
Click the first command button on the user form. In the Properties Window, next to "(Name)," change "CommandButton1" to "OK_btn." Next to "Caption," change "CommandButton1" to "OK."
Click the second command button on the user form. In the Properties Window, next to "(Name)," change "CommandButton2" to "Cancel_btn." Next to "Caption," change "CommandButton2" to "Cancel."
Add a picture to the user form. In the Controls Toolbox, click the image control, which looks like two mountains and the sun. Click the user form to place the image box on the form.
Click the image control you placed on the user form. In the Properties Window, next to "Picture," click the three dots (...) on the right. Browse to a picture saved on your computer, such as C:\Users\Public\Pictures\Sample Pictures\Garden.jpg for a display of orange flowers. Double-click the selected picture.
Move and resize controls on the user form until you are satisfied with the design.
Double-click the "OK" button on the user form to open a code window. You will see the beginning and ending statements for the subroutine.
Add code between the "Private Sub" and "End Sub" statements so that it reads as follows:
Private Sub OK_btn_Click()
Dim LastRow As Long
LastRow = Worksheets("Sheet1").Range("A65536").End(xlUp).Row + 1
Cells(LastRow, 1).Value = Name_txt
This code places the name from the text box into Cell A1 on the Excel spreadsheet. Each time the user enters a new name and clicks "OK," it will be added underneath.
Double-click UserForm1 In the project window. Double-click the cancel button. In the code window, add code between "Private Sub" and "End Sub" so that the routine reads as follows:
Private Sub Cancel_btn_Click()
This code hides the dialogue box when the user clicks "Cancel."
Execute the routine. Select Run > Run Sub/UserForm from the menu. You will return to Excel and see the dialogue box, including your selected picture.
Type a name in the text box and click "OK." You will see the name in Cell A1. Type another name and click "OK." You will see the name in Cell A2.
Click "Cancel" to hide the dialogue box.
Tips and warnings
- If you do not see the Developer tab, click the "Microsoft Office" button. Click "Excel Options" on the bottom right-hand side of the drop-down menu. Select "Popular" in the left-hand pane. On the right side, under "Top options for working with Excel," check the box next to "Show Developer tab in the Ribbon." Click "OK."
- If VBA code does not run, adjust security settings in the Trust Center. Click "Macro Security" in the "Code" section of the "Developer" tab.
- 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