Show Buttons
Share On Facebook
Share On Twitter
Share On Google Plus
Share On Linkdin
Share On Reddit
Contact us
Hide Buttons

VBA-Excel: User Forms

User Forms are one of the very impor­tant fea­tures in Excel Macro. It pro­vides and an inter­face to the user for inter­ac­tion with the work­book. It has many con­trols which makes user’s life easy for enter­ing the data from excel in a spe­cific man­ner or read­ing the par­tic­u­lar data from the excel.

Get­ting Started : Cre­ate a Blank User Form

  1. To open or cre­ate a new user form, fol­low the steps below
  2. Open an Excel file
  3. Press “Alt+F11” to open the Microsoft Visual Basic Editor.
  4. Make Project Explorer and Prop­erty Win­dow vis­i­ble from “view menu” (If not already visible)
  5. Select the Project from the Project Explorer.
  6. Form “Insert” menu, select “User Form
  7. User Form - Create User Form

    User Form — Cre­ate User Form

  8. You will see “UserForm1” under “Form” sec­tion
  9. One the right side you will see a User­form .
  10. User Form - Insert New Form

    User Form — Insert New Form

  11. Make All Con­trols (Tool box) vis­i­ble from “View menu” if already not displayed.
  12. User Form - Tool Box

    User Form — Tool Box

  13. Look for all the Prop­er­ties of the User Form in Prop­erty Window.
  14. User Form - Property Window

    User Form — Prop­erty Window

  15. Give a Proper Name and Cap­tionto the UserForm1,
    1. Select the Userform1
    2. Go to the Prop­erty Win­dow,
    3. Change the Name Prop­erty to “First­Form
    4. Change the Cap­tion Prop­erty to “My First User Form
    5. User Form - Name and Caption

      User Form — Name and Caption

16.  Add Con­trols  to the User form

  • Select the con­trol from the Tool Box and place it in the User Form.( here Com­mand But­ton is selected)
  • Select or Sin­gle Click on the Com­mand But­ton from the Tool Box.
  • Now Click on the User­Form, a new Com­mand­But­ton will be created.
  • User Form - Add Command Button

    User Form — Add Com­mand Button

  • Change the But­ton Name and Cap­tion name in the Prop­erty Window
  • User Form - Command Button

    User Form — Com­mand Button

  • Dou­ble Click on the but­ton to view the Click func­tion of the Button(Actually when you dou­ble click it first time, then only “Click” func­tion will be created.)
  • Inside the func­tion write Msg­box “Yeaaaah­h­hhh , I got Clicked”.Private Sub

          Pri­vate Sub FirstButton_Click()
                 Msg­Box “Yeaaaah­h­hhh , I got Clicked“
          End Sub

User Form - Command Button click

User Form — Com­mand But­ton click

NOTE : To Nav­i­gate Between “Code Win­dow” and “User­Form Design Win­dow” KeyB­o­rad Short­cut 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 “Mod­ule
  3. User Form - Insert Module

    User Form — Insert Module

  4. A new Mod­ule will be cre­ate in the Project Explorer under Mod­ules folder
  5. Click on the Mod­ule 1, Code win­dow will be dis­played on right side.
  6. Cre­ate a Sub-Routine say with name “Sub Sumit”
  7. Dis­play the User­Form inside the Sub-Routine using Show() func­tion.  
  8.      Sub Sumit()
        End Sub
  9. User Form- Sub Routine

    User Form– Sub Routine

  10. Cre­ate a but­ton in Excel Work­Sheet and assign the Sub-Routine to it ( Click Here)
  11. Click on the But­ton on Excel
  12. User­Form ( First­Form) will be displayed.
  13. User Form - Display

    User Form — Display

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

User Form — Dis­play — Clicked


You may also like...

Leave a Reply

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

%d bloggers like this: