Show UserForm When Excel File Opens

To help users enter data in a workbook, you can create an Excel UserForm, with text boxes and combo boxes.

userformstart00

In the original UserForm sample file, there was a button on the worksheet, and users could click it to open the UserForm.

userformstart01

In some workbooks, you might want people to get right to work, and see the UserForm immediately. So, instead of putting a button on the worksheet, you can show the UserForm automatically, when the file opens.

Open the Form Automatically

To make the UserForm open automatically, you’ll add a bit of code to the workbook’s code module, in the Workbook_Open procedure.

To add the code:

  • In the UserForm workbook, press Alt + F11, to open the Visual Basic Editor.
  • In the Project Explorer, find the UserForm workbook, and double-click on its ThisWorkbook module.

userformstart02

  • From the Object drop down, select Workbook

userformstart03

  • From the Procedure drop down, select Open
  • The Workbook_Open procedure will be automatically created.

userformstart04

  • Where the cursor is flashing, type a line that opens the UserForm. In this example, the UserForm is named frmParts:

userformstart05

Test the Workbook_Open Code

To test the Workbook_Open code:

  • Save and close the UserForm workbook.
  • Open the workbook, and enable macros, if prompted.
  • The UserForm will open automatically.

______

You may also like...

26 Responses

  1. Morton Wakeland says:

    3 Tables are in a workbook, one on each differently names tab, e.g. Tab A, B, C.
    When workbook is opened the built in “data entry form” shows for Tab A
    Cannot figure out VBA code to add so that when Tab B is selected or Tab C is selected – the
    “data entry form” for that worksheet is displayed.

    Thank you so much!

  2. tomas says:

    Hi Morton.. Im not sure how to define to check what Tab is selected.. But maybe try it this way (i did the same in past)

    In some cell that is free to be used, lets say A1, put Tab name into.. I mean like in Tab A, put “A” into A1cell. In Tab B, put “B” into A1cell. In Tab C, put “C” into A1cell.

    lets say you have dataentries called -userformA, userformB, userformC-

    Then define in VBA to check cell A1 value, no matter what sheet is selected.. And based on value correct userform will be showed… Something like this.. (change it to fit your values, ranges and names)

    ……..
    Private Sub Workbook_Open()
    range(“A1”).select
    if selection.value = “A” then
    userformA.show
    else
    GoTo 2
    end if

    2:
    if selection value = “B” then
    userformB.show
    else
    GoTo 3
    end if

    3:
    if selection.value = “C” then
    userformC.show
    else
    exit sub
    end if
    end sub

  3. John says:

    I am new to coding. I am following your instructions to open my form automatically. The form’s name is UserForm1, which I am adding the ca\ode as follows.

    Private Sub Workbook_Open()
    frmUserForm1.Show
    End Sub
    Keeps asking for a variable, not sure what that is. Any help would be greatly appreciated.

  4. John says:

    Debra:

    Thank You for the reply. Worked great!

    John

  5. Sean says:

    John,

    I have a module inserted into a combo box to show options for users to choose. I can get the form to auto open, but the module won’t populate the combo box. I am using excel 2011 for Mac. The module is called ‘module 1’. Any ideas?

  6. John says:

    Sean:

    I took my list that I was using for the combo box and created dynamic name ranges. Under formulas you can click on Define names and create this. I also copied the data I am using and in the upper left hand corner of the spreadsheet you will see it is labeled as A1, change this to something easier like DEPARTMENTS so you will know what it is. Go to your combo box, click in the field and click on properties. Look on the left hand side for RowSource, enter the name you gave your dynamic range, mine is DEPARTMENTS. This should allow the range to populate to the combo box.

    Good Luck

    john

  7. Sean says:

    Is there a row source in the Mac version? I didn’t see one.

  8. Jubert says:

    Hi,

    I did this and everything was running smoothly, however when I tried to close It again suddenly I got an error
    of Run-time error ‘438’: Object doesn’t support this property or method.

    Private Sub Workbook_Open()
    UserForm1.Show
    End Sub

    got any ideas out there?

    cheers
    Jubert

  9. Frankie says:

    thank u sooo much….

  10. خرید کریو says:

    thank you

  11. Sean says:

    Private sub userform_initialize

    ListBoxName.List = Range(“LISTNAME”).Value

    End sub

    ‘You will have to delete the row source the properties box for the listbox.(option not available for Mac users)’
    ‘Also, make sure that you userform NOT your userform name after Private sub.’

  12. Kal says:

    I want to save the automatic input form that excel 2013 automatically creates from the quick access toolbar ‘form’ which uses my worksheet data. I’d like to save it as a userform object under my Developer tab (VBE).

    • Maureen says:

      Hi Kal, Did you get an answer to your question? I am trying to do the same thing and can’t figure it out. I want to use the automatic form generated by excel 2013 and have it open automatically in the worksheet and no luck so far.

  13. Sarfaraz says:

    Hi All,

    when i always open my userform my background excel are disabled to do multitasking work.

    please suggest me…

  14. Peter says:

    I have managed to close my workbook and opened just the userform whenever I click on the workbook, but I cant get back into the VBA editor and change the form whenever I want to make adjustments. Every time I close the userform it doesn’t open the editor or workbook. Any ideas??

  15. Anna says:

    How do i know what my useform’s name is? I made the userform in the worksheet not in VBA.

  16. Arthur Wrightis says:

    If you don’t know what you called it, more than likely userform1. Check by clicking on the developer tab, click the editor button, and it should show you a project window with all items associated with worksheet. Click on associated user forms to confirm.

  17. raffa says:

    hi everybody,

    I want to create a Combobox with the name of every sheet of my workbook in it and when value is chosen in the combobox it will bring up a next Userform according to the sheetname.In the new userform what opens i want create a preview print button of that sheet and a go back button to the first userform. Can somebody help me with the codes to create this?? Im new to writing codes but this will help me alot.

  18. hassan says:

    i want to know how to get code. please

  19. Michael Sanchez says:

    Thanks. This really helps

  20. Divya says:

    Hi, Can you tell me what is the coding for bring the data in our desired row using macro after created the userform.

    Example,

    Everyday i am entering data in excel manually.One day i created userform to enter data in that excel.after done everything(create userform&coding),when i am run that userform,the information which i put in the userform overrights the existing line which i typed manually.

    The userform data’s doesn’t start from blank cell or row.it’s over right the existing cell
    I need the assistance for this please

  21. Eswari says:

    Small question. I can auto open the form as per your instructions for my team. But if I want to edit it, how can I do it. As on click, it loads the form always. How to open the work book for editing .

    • Heli88 says:

      open the code editor, go to the project tree and open the code from Book1 inside Microsoft Excel Objects.

      You should have something like this:

      Private Sub Workbook_Open()
      UserForm1.Show
      End Sub

      Just erase the second line ! Just leave the workbook sub / end sub.

      cheers

Leave a Reply to hassan Cancel reply

Your email address will not be published. Required fields are marked *