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.
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".