Now this tutorial will show you how to programme the basic user form that you created in the last tutorial. I change a few things on the form to make it easier to programme. One of the changes I have made was to put the male and female option buttons inside a frame all these tools where found in the toolbox that appears when creating a user form. I also added in a clear button so you can clear the form easily after you have filled it in.
The first thing we are going to programme is the close button as this is the easiest part. When you are looking at the UserForm in the editor double click on the close button and it should bring up the module ready for you to type in some code. All you have to type is “Unload Me” and this will close down the UserForm when the button is clicked.
The next thing to programme is the Clear button. This is also fairly easy to do as its just two commands. Double click on the button like before and then entre in “Unload Me” then “Nameoftheform.Show” e.g. UserForm1.Show. This will close the form then reopen it.
The next part is to start coding to the add to list button. This is the hardest part because there is a lot more to put in it which means a lot more could go wrong. The first section you will want to write is the range now this will be where the data is taken from the form and out into a spreadsheet as you can see I set my range as A1 to H1 this means the first set of data will be entered in that range. The next line of code means that after the last piece of data is entered in H1 it then means the next lot of data will be entered in the next row (A2-H2) and the last line in this section just means that it will look for the next row that doesn’t have any data in it.
Now we can start programming it so the data from the fields is entered in to a spreadsheet. To do this you need to write the following “ActiveCell.Value = NameofFiled.Text” this will take the data you have entered into that specific filed and place it into a spreadsheet. The line of code after that is to make it move to the next column but stay on the same row this means the data can be entered in to a table like structure.
Now we need to code the reaming fields to entre data into a spreadsheet. It’s just like the names before you write it in the same way but you just got to remember to write the correct name of the filed.
The only one that is slightly different is the gender on because you have option buttons. So for this code just copy as I have done it below. After this you should test it to make sure it works.
You are now going to want to create a new module then create this macro with in there. What this macro does is it will display a message box saying would you like to use the UserForm and you can click yes or no but if you click yes it will open the UserForm up ready for you to input your data. The last part of the code says “UserForm1.Show” you will need to change UserForm1 to the name of your user form. Once you have done that you can add it to a button or the ribbon so you can quickly access it. To learn how to add it to the ribbon visit our blog post called “How to run and add your macros to your ribbon” and to learn how to add it to a button read our post “How to add a Macro to a Button” or take a Excel VBA course which will teach you how to do this and a lot lot more.
Today I will be staring to show you how to create a user Form in Excel VBA. First thing you are going to want to do is to open up the Editor and go to insert and then down to user forms. This will create a blank user form ready for you to edit.
Next you will want open up the Properties window for the user form. To see this you need to go to view tab then down to Properties Window this will display a window on the left had side. This is a very useful window because it has things like the name of the form in it and other things like colour or size of the form. You should always name your form with something which is easy to remember and is relevant to the form as this is how you refer to the form within your code but to change the display name of the form you have to change the caption located under border style. You should always change the properties before you write your code because if your write the code out with it called UserForm1 and then at the end change it to Master Form you will then have to go back though your code and change it all. I won’t be changing any of my names.
Now we are ready to start inputting things into the UserForm to do this we use the tool box and this will appear when you create the UserForm if you have closed it then you can get it back, just go to the view tab and then go down to Toolbox. Form this menu you can insert a range of different things from text boxes to pictures.
To start off my UserForm I have put in some text boxes by selecting them from the Toolbox and you draw them out like you would do in Word or PowerPoint. I then just copied and pasted them below one another. You can use the property window to change the colour and other things just like before with the UserForm.
Now we have got some text boxes we need some titles for the text boxes to do this we need to use the label tool from the tool box, it looks like a capital A. To change what it says in the label you need to change the caption within the Properties window. This will not change how you refer to it in your code.
You might a filed which is for the date of birth but a text box is not a good thing for them to input there answer in so we are going to have to change it. So delete the old text box and find the Combo Box tool from the tools box and draw out 3 boxes making sure they are big enough for the date that will be going in there.
Now you won’t have any data linked to it so you won’t be able to choose anything when you click on the down arrow. To link the data you need to have it in a spread sheet, I created a new worksheet for this and then just put the data in to the first 3 columns. To link them together you need to click on the Combo box you want then go to the properties window and scroll down till you find a filed called RowSource and then type in “sheet2!a1:a31” ( you might need to type in something different depending on where the data is located but it’s always set out like this “NameOfSheet!StartingCell:FinsihingCell” but without the quotation marks). You will need to repeat this for the day, month and year.
Another filed in your form you might have is gender and you will probably want to change the text box for something else. I have chosen to use the option buttons for this but you could use what you like. To change the displayed name of the option button got to the properties window and change the caption to what you like. Now you are able to create a simple UserForm to learn how to programme read our next blog post "How to programme a basic UserForm".
The Excel VBA editor has an Immediate Window that lets you type instructions and test expressions. It will run statements immediately as if you they were run from a procedure.You are also able to precede variables and expressions with a question marl to perform what is operation. For an example, ?Workbooks.Count asks “How many workbooks are open?”. To find the Immediate window you have to go to the View tab and then down to Immediate Window and it will appear at the bottom of the VBA editor. An Example of what you could write in here is Workbooks.Add and this will open a whole new workbook.
Clicking a command button or selecting an item from a list are examples of an event driven language this is where code can respond to a specific event. The underlying procedure, called an event handler, will run every time a particular event occurs. For example, you may specify an event handler to validate the user’s input or to confirm a requested action. There are courses where you can learn all of this and more Excel VBA techniques and features.
For every event associated with an object VBA has set up a mini procedure called event handlers that respond to the event. These are the Sub and End Sub statements you see at the start and end of your code. You add in your own VBA statements in-between these statements to specify how the event is to proceed.
To define an event handler for a form:
1. Select which form you want
2. Then display the code module
3. Select your desired event from the procedure list
4. Enter the rest of the procedure between the Sub and End Sub statements.
Below you will see a code module for the form object, you should notice that UserForm has been selected by default in the Object List and the Click procedure is currently active in the module. The screen shot also shows you a portion of the events recognised by the UserForm object.
Object list – This shows which object you are currently working with. If you select an object rom this list other than (general), you can specify event handlers for the object. If you select (General), you can use the module window to entre only standard VBA procedures and Functions.
Procedure List – This will show you which procedure is active in the code module. If you have selected (General) then it will display all the standard VBA procedures and functions. If you have chosen something different like the example above then it will show you all the events recognized by the object.
By building your own customised dialog boxes ( or UserForms as they are known in VBA) with many controls as you need, command buttons, check boxes, list boxes and so on. You can also build a simple user interface for your VBA application. The Editor makes the construction of even the most sophisticated dialog boxes as easy as dragging and clicking the pointer.
Adding a Form to Your Project
A form is a separate object that you can add to your VBA project. When you insert the a user form VBA will perform the following tasks:
· It displays the Toolbox
· It creates a new UserForm object and adds it to the forms branch
· IT adds a Forms branch to the project tree in the Project Explorer
· It displays the form in the work area with a default name such as UserForm1
Inserting Controls Into a Form
When you create a new Form it is basically an empty shell until you populate it with controls then it becomes useful. The Toolbox has a set of 14 default controls which you can insert. Including command buttons, which let the user accept the form data, cancel the form or carry out another command with a click if the mouse. Labels which let you adds text to the form, text boxes which let the user enter text and numbers into the form and Check boxes which let the user select options that can be toggled on and off.
Form and Control object Properties
The control objects that you add to a form and the form have an extensive list of properties that you can manipulate by entering or selecting values in the properties window. Some examples of form-level properties include font which will set the default font for the form. CenterScreen will position the form in the centre of the screen and BackColor will change the background colour of the form.
You could attend a Excel VBA course which will teach this and more in a lot more detail.