Excel For Mac Vba Userform

Other people can use Excel to fill out the form and then print it if they choose to. Step 1: Show the Developer tab. Newer versions. What Is A UserForm. When working with VBA, you usually use the term “UserForm” to refer to dialog boxes. A UserForm is an object. This object represents a window or dialog box within Excel's User Interface. By working with the UserForm object, you can easily create custom dialog boxes.Each of these dialog boxes you create is held within an UserForm object.

In this article, will learn the basics of VBA userforms. I will explain how to create a form in excel, how to use VBA toolbox, how to handle user inputs and finally how to store the user inputs. We will go through these topic using one example and step by step guide. If you are reading this, than I assume you know the basics of Excel VBA.
Without further delay, let’s get started.
Create A Form for Investment in Excel and Store Record
We will start by designing the user form. In this userform, we will have one textbox for name, one for age, one for investment amount and a pair of radio buttons for gender. Let’s start it.

Design the userform

MakeMKV for Windows and Mac OS X is available for download below. MakeMKV for Linux is available on the forum page. Requires Windows XP or later, x86 or x64. Requires x64 Mac OS X 10.6 or later. MakeMKV 1.14.7 Crack MAC/Win Registration Code MakeMKV 1.14.7 a crack is an amazing tool that allows you to convert any video to MKV format. This format is good to convert fast media files like DVDs and Blu-ray discs to MKV. MakeMKV Crack With Torrent Full Version Comparativelively, the MakeMKV final Registration Code is the extension of the file of Matroska Video. The format of this file used widely; it can contain the information for whole movies into a single file. Makemkv torrent mac.

• Open the visual basic editor in excel using ALT+F11 shortcut. In Project Box, right click on VBAProject(yourfile). In insert option, choose UserForm.
• Immediately, a new folder will be created and your userform will be found their. All userform for this project will be added in this folder.
• Change the name of form to InvestmentForm in the property box.

The property box is shown in the lower left of the project explorer. If you can’t see it, go to view and click on Properties Window. We will be using it a lot, so make sure it is there. It is used for styling, naming and customisation of forms.


• Add elements to form: In above image I have already added elements (labels, textbox, command button). However a new userform is completely blank.You need to use tool box to add elements to your form. If you can’t see the toolbox, get it from view tab.


Add labels using toolbox for descriptive names. Add textbox for user inputs.

Add two radio buttons and name them Male and Female.

I have used a frame to encapsulate them, but it is not necessary.

Add command buttons submit and cancel to perform operation on given input.

• Name Elements: The textbox, labels, buttons, etc. all are elements. And in order to use them in VBA code we need to give them names. We use property window to change their name.
• Select the element. Here I am select the textbox for name. Go to property window and change the name to “NameBox”. Do the same for each element you are going to use. (you don’t need to name labels, unless you want them to be clickables.)

I have renamed the element as below table and I will use these names to refer to them. You can have different names. Just replace these names with your name. These are VBA names (code name) that will be used in code. They will not be reflected on form.
The text you can see on labels and buttons are “Captions”. The name and caption of element can be same, if you want.

• Show User Form to User: Now the form is ready, let’s show it to the user. But wait, how do I do that. There’s no option on worksheet to call user form.

Actually, user form needs trigger. It can not be shown by itself. You can use, a command button, a subroutine or an event to make form pop up on the screen.

Here I am going to use a command button to trigger user form.

• On a worksheet, go to developers tab? Insert? Button (form control). Rename it open form.
• Right click on it. Click on assign macro and then click on new.
• A sub will be created immediately. Now add this line to that sub.

It is done. Go back to that sheet and click on the button. The user form will pop up.

• Fill sheet using VBA user form: As we clicked on command button (open form) the form pops up. Now you can fill the form. But when we click on on submit button, data should have been entered on this sheet, but nothing happens.

Because we have not written any vba instruction for that. We need to store user form data into sheet.

• Go back to VBA and double click on the Submit button. A new sub will be create automatically. This sub is embedded in the form and you can’t find it in any module. This is same for each element of form.
• Write this vba code in that sub.

The above snippet of VBA code runs when submit button is clicked. It finds the first empty row on the sheet and fills it with values provide in form. And in the end it closes the form by using command “Unload Me”.

• Use Cancel command to close the form. For now, cancel button is useless. It is doing nothing. In case if you want to abort the input, you can use cancel button. In that case:

Double click on the cancel command button in VBA editor. A new sub will be created. Just write this line of code to close the user form.

Finally the form code will look like this.
And that’s it. This is how you use excel vba userform to get input from users. In this article, we just explored the basic use of userform, so that you get familiar with it. It is simple, if you have knowledge of basic vba.

In future articles, we will explore more advanced functionalities of vba userforms in Excel. We will create userform in excel to do a lot more. We will explore different tools and elements available to vba userform. Till then practice this. You can download this file for reference if you want.
So yeah guys, this was a small and simple userform tutorial. I hope it was resourceful. Let me know if you have any doubts regarding this, let me know in the comments section below.
Related Articles

Return the cells referenced by user using RefEdit control of userform in Excel
Popular Articles:
The VLOOKUP Function in Excel