How to Insert a Picture Dialog in Excel 2007 VBA

Written by jenny westberg
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Insert a Picture Dialog in Excel 2007 VBA
Create an interactive dialogue box featuring an image or photograph. (computer girl image by PD-Images.com from Fotolia.com)

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:
Easy

Other People Are Reading

Instructions

  1. 1

    Open the Visual Basic Editor. Click "Visual Basic" in the "Code" section of the "Developer" tab.

    How to Insert a Picture Dialog in Excel 2007 VBA
    Your user form can include a picture of garden flowers. (flower in art form image by Gary from Fotolia.com)
  2. 2

    Select View > Project Explorer from the menu, then select View > Properties.

  3. 3

    Select Insert > UserForm from the menu. You will see a blank user form and the Controls Toolbox.

  4. 4

    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.

  5. 5

    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.

  6. 6

    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.

  7. 7

    Click the label on the user form. In the Properties Window, next to "Caption," change "Label1" to "Enter Name."

  8. 8

    Click the text box on the user form. In the Properties Window, next to "(Name)," change "TextBox1" to "Name_txt."

  9. 9

    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."

  10. 10

    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."

  11. 11

    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.

  12. 12

    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.

  13. 13

    Move and resize controls on the user form until you are satisfied with the design.

  1. 1

    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.

    How to Insert a Picture Dialog in Excel 2007 VBA
    Build a list of names on your Excel spreadsheet. (man with computer image by Bionic Media from Fotolia.com)
  2. 2

    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

    End Sub

    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.

  3. 3

    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()

    UserForm1.Hide

    End Sub

    This code hides the dialogue box when the user clicks "Cancel."

  4. 4

    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.

  5. 5

    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.

  6. 6

    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.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.