Be the first user to complete this post

  • 0
 Add to List

VBA-Excel: User Forms

User Forms are one of the very important features in Excel Macro. It provides and an interface to the user for interaction with the workbook. It has many controls which makes user’s life easy for entering the data from excel in a specific manner or reading the particular data from the excel.

Getting Started : Create a Blank User Form

  1. To open or create a new user form, follow the steps below
  2. Open an Excel file
  3. Press “Alt+F11” to open the Microsoft Visual Basic Editor.
  4. Make Project Explorer and Property Window visible from “view menu” (If not already visible)
  5. Select the Project from the Project Explorer.
  6. Form “Insert” menu, select “User Form”

User Form - Create User Form
User Form - Create User Form
  • You will see “UserForm1” under “Form” section
  • One the right side you will see a Userform .

    • User Form - Insert New Form
    User Form - Insert New Form
  • Make All Controls (Tool box) visible from “View menu” if already not displayed.

    • User Form - Tool Box
    User Form - Tool Box
  • Look for all the Properties of the User Form in Property Window.

    • User Form - Property Window
    User Form - Property Window
  • Give a Proper Name and Captionto the UserForm1,
    1. Select the Userform1
    2. Go to the Property Window,
    3. Change the Name Property to “FirstForm”
    4. Change the Caption Property to “My First User Form”

    • User Form - Name and Caption
    User Form - Name and Caption

    16.  Add Controls  to the User form

    • Select the control from the Tool Box and place it in the User Form.( here Command Button is selected)
    • Select or Single Click on the Command Button from the Tool Box.
    • Now Click on the UserForm, a new CommandButton will be created.

    User Form - Add Command Button
    User Form - Add Command Button
  • Change the Button Name and Caption name in the Property Window

    • User Form - Command Button
    User Form - Command Button
  • Double Click on the button to view the Click function of the Button(Actually when you double click it first time, then only “Click” function will be created.)
  • Inside the function write Msgbox “Yeaaaahhhhh , I got Clicked”.Private Sub

    •           Private Sub FirstButton_Click()
                     MsgBox "Yeaaaahhhhh , I got Clicked"
              End Sub

    User Form - Command Button click
    User Form - Command Button click

    NOTE : To Navigate Between “Code Window” and “UserForm Design Window” KeyBorad Shortcut is [Control]+[Tab]
    17. Now your User Form is ready to run. All you have to do is assign this User  Form to a macro.

    Macro to Open the UserForm

    1. Select the VBA Project from Project Explorer
    2. Form “Insert” menu, select “Module”

    User Form - Insert Module
    User Form - Insert Module
  • A new Module will be create in the Project Explorer under Modules folder
  • Click on the Module 1, Code window will be displayed on right side.
  • Create a Sub-Routine say with name “Sub Sumit”
  • Display the UserForm inside the Sub-Routine using Show() function.  
  •      Sub Sumit()
                FirstForm.Show
        End Sub

    • User Form- Sub Routine
    User Form- Sub Routine
  • Create a button in Excel WorkSheet and assign the Sub-Routine to it ( Click Here)
  • Click on the Button on Excel
  • UserForm ( FirstForm) will be displayed.

    • User Form - Display
    User Form - Display
  • Click on the CommandButton(“Click Me”)
    • User Form - Display - Clicked
    User Form - Display - Clicked

    Also Read:

    1. Excel-VBA : Change Passwords for all the WorkSheets in one shot
    2. VBA-Excel: WorkBook.Save Method
    3. VBA-Excel: Fill Excel Range Values in a 2D Array
    4. FileSystemObject:OpenTextFile Method
    5. Excel-VBA : Prevent Adding New Worksheet