VBA (Visual Basic for Applications) is the programming language used to create macros. A good knowledge of VBA can save time and make you a much more productive Excel user. VBA is a common programming language found in virtually all Microsoft Office applications. People will often learn VBA before progressing to other languages in due course.
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.