Show UserForm When Excel File Opens

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. Then, with a quick bit of code, you can show UserForm when Excel file opens, so the form is easy to find and use.

Parts Inventory Data Entry Form

Here is the parts inventory data entry form from the sample file.

Show UserForm When Excel File Opens

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

userformstart01

Open the UserForm Automatically

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, for people to click, you can show the UserForm automatically, when the file opens.

Code to 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.
  • At the left, in the Project Explorer, find the UserForm workbook, and double-click on its ThisWorkbook module (in the Microsoft Excel Objects folder).

userformstart02

  • At the top left of the main code window, from the Object drop down, select Workbook

userformstart03

  • At the top right, from the Procedure drop down, select Open
  • The Workbook_Open procedure will be automatically created, with the cursor positioned between the first and last lines of code.

userformstart04

  • Where the cursor is flashing, type a line that opens the UserForm. In this example, the UserForm is named frmParts, so type the following line of code in the Workbook_Open event:
    • frmParts.Show

userformstart05

Test the Workbook_Open Code

To test the Workbook_Open code, follow these steps:

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

______

32 thoughts on “Show UserForm When Excel File Opens”

  1. 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. 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. 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,
    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?

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

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

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

  8. 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).

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

      1. Kal and Maureen:
        I just did this as I wanted same. Over the years you may have no use for this now. But it may help others:
        In Excel on your sheet where you are launching the auto form, run a macro.
        1. Record the macro of you opening the form. Do nothing else but open the form and stop running the macro.
        2. Alt+F11 to get into programming screen.
        3. You will see on the left a module1 was created. Click it.
        4. You will see the macro that was recorded and at the end just before End Sub – you will see the action. Mine says: ” ActiveSheet.ShowDataForm” – copy this.
        5. Now go to “ThisWorkbook” on the left and copy the process mentioned in this article ( dropdown select, Workbook).
        6. Then your code inbetween Sub and End Sub you will paste the macro action in the middle. So, instead of what this article says of “FormName.show” just paste what you copied from Module1.
        7. Close and save and it should load. You will have the excel sheet in the background but the form launches and is workable.

        Hope that helps.

  9. Hi All,
    when i always open my userform my background excel are disabled to do multitasking work.
    please suggest me…

  10. 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??

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

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

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

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

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

  15. Hello , First of all Thank you for your suggestion. I have tried this code but when userform appears on the screen , when i am click outside of the form ,the form is hidden permanently . can you please tell me some alternative of this problem.Thanks in Advance.

  16. Hi Guys
    I have a problem in that when I use this code (UserForm1.Show) I get the following error message:
    Run time error 424: Object required
    How do I solve this?

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.