Creating a Form with Vba Excel Alternate Version

Creating a form with vba excel alternate version

what we are going to do is create a user form using VBA for Excel and on that form have a range of labels and text boxes that enable data to be typed in .We will then create the code to drag the data across to the worksheet.We will also write the code to clear the data on the userform.Once we have created the label and the text box next to it we must go to properties and name it appropriately.This is very important.

how to get to vba :

Open excel -click developer tab-go to vba –select insert drop down –then userform

Right click the user form to change properties of the form such as background colour

Now we need to name the userform in properties -Change the caption to Inventory and the name to frmInventory

Now we are going to insert a label –once inserted we need to name it –lblitemname.Then change caption to item name

Now we need to create a label called Price and a text box to go next to it .Then we need to create a label called quantity and place a text box next to that .You must make sure that you change the name and the caption for each thing you are inserting on your form

Now we are going to add a combo control button for quantity –select the toolbox-select combobox

Now we need to specify something called rowsource.scroll down the properties list and select rowsource and type in qty

Nb :Before you type in the name qty in row source you must go to sheet 2 in your spreadsheet and specify a range

This will then take you back to this view

Now we’re going to insert a command button

Now double click on workbook in the top left list area of your vba page

Select the drop down and select workbook.Select the drop down and select open(this is something called a procedure)

Now we need to type in between Private sub and end sub the following code

You must save your vba code as a macro enabled workbook.That way when you open it the form will run.

When you open it, it will look like this

Now you need to step into the vba code by exiting the spreadsheet and then going back to the vbacode.you need to type in the code to transfer data to the spreadsheet.seelct the transfer comman button then in code area on left hand side type in the code that we want to apply to this button

Once you have typed this code in then you need to saved your vba code as a macroenabled workbook

Now we are going to add a command button to clear the data that has been entered from the form

Draw it on as before , then name it in the properties.Name the button as cmdclear and change the caption to clear

Now double left click on the button and step into the vba.Type the following code in between private sub and end sub

Your overall code will look like this:

Now we are going to add a final button to our form to end the program

Save the final vba code and save the work book as an enabled work book

1 | Page