How to create userforms in excel VBA?

This part will go through the VBA userform.

Personally I haven't worked a lot with forms in VBA other than making a calender and some other basic stuff as I mainly have focused on analyzing data in a good and efficient way. However, I thought I would touch upon the subject so that you get a sense of what it is and how it works.

Userforms can be good to customize forms and in my personal opinion it is much more stable than using any activeX control, and it can be a great way to guide the user through different steps and embed your code in the form.

My Form

I created the following form, and added 2 buttons, that displays 2 message boxes just to demonstrate how it works. 'Pls Fix Thx' button displays a message box saying "Hello Analyst!" like below. 'Boss' button displays a message box saying "Work harder! /Your Boss".

Now let's go through how I made it.

Instead of right clicking and creating a new module, we will choose userform, and that will give you the following screen. Toolbox - Here is where you get your modifications from. Importing pictures, textboxes, labels, togglebuttons.. etc. If the Toolbox doesn't show up for you immediately, just go to "View/Toolbox" and it should show up. Userform quick edit - This is the small box below the Toolbox to the right. This box allows you to center, and group things more quickly rather than manually doing it. You can find it under "View/Toolbars/UserForm" if it doesn't show up for you. Userform - This is the form you are creating in the middle of the screen.

Properties - Same as in your normal macros you have the properties pane in the left bottom corner. This is more used now when you are editing your userform. Here is where you change the colors, edit the texts, and add other specialeffects. A common question is How to change the Font Size. If you simply double click on the Font in the properties window you will get a large pop-up displaying all Font Size and Style options.


When you have added an item, if you double click on the item in the userform your normal code window will be displayed. You do have a lot more actions than just the 'Click' action which is when a user clicks on the button in your real form once you start playing around with it.

My Code for the project was the following.

Private Sub CommandButton1_Click()
      MsgBox ("Work harder! /Your Boss")
End Sub

Private Sub CommandButton2_Click()
      MsgBox ("Hello Analyst!")
End Sub

Lastly, Outside of my userform I added a normal macro button on the worksheet. and set the code as .Show in order to display my userform when the user click on the worksheet button.

Sub UserForm()
End Sub

0 views0 comments