How To Link Raw Data Into Vba Code

In today's case we'll introduce data entry with the help of the UserForm. Using UserForm can considerably better the interaction with our users. Furthermore this manner we can assist them to execute particular steps.

They play a very important role in the automatization of Excel. In these days none of the concern dashboards can be imagined without Form Controls.

Let's say a few words about today'south task. At times data entry can exist very boring and by this can incline users to mistakes. We'll design a form that based on three data (Name, Age, Chore title) volition upload a "database".

Exactly as yous can run into on the flick below!

What can exactly be seen on the picture?

Three label, three text fields. And another matter, the Command Push button. We'll assign to this a simple macro that will store input information.

[adinserter block="ii″]

How to create a UserForm to assistance data entry?

Let's offset! The commencement step is to enter the Visual Basic Editor form the ribbon or by pressing the Alt+F11 buttons.

Open the VBA Editor. If the Project Explorer is not visible, click View, Project Explorer. Click Insert, and select the Userform.

After this identify the fields and their names onto this form.

Now insert the names of the fields by clicking on the Label Command button:

Equally next step follow the TexBox belonging to the names. This option enables the data insertion.

Change any elements and attributes of our form by right-clicking on them and then choose the Properties window.

As next step we have the option for example to set the fonts, size and colors.

We can be all creative regarding the designs. Nosotros tin can make whatever kind we'd like or what we need for a given task.

At present we will identify some other button on this course. The purpose of this is to set the given data on one Excel table.

We do this past inserting the CommandButton from the Controls:

Let's see what the information entry form looks like at present:

The user interface is ready! Data input will exist a lot easier from now on. But nosotros all the same have one very important thing to do.

Because for the next step nosotros have to determine the code backside it.

In this instance this would run by clicking on the "Add to list" button.

Let's right click the CommandButton.

From the drop down menu cull the View Code command. In the next chapter nosotros'll show how to assign a short VBA code to this.

Adding Logic to Button in a Form

Let's clear what practice we expect from the code afterward clicking on the button?

Our expectation will be that information technology inserts the values of the UserForm into the determined cells of the adamant rows.

For this nosotros have to know where the next empty line in the tabular array is.

Later on the insertion nosotros have to equal the advisable cells to the input values of the appropriate fields.

To brainstorm permit'south ascertain two variables.

The first one with Integer type past the name rw to make up one's mind the current, still empty rows.

The second 1 by the name ws and with type Worksheet.

This will determine which worksheet our form will refresh / update the cells.

We can already set the calues of the latter variable onto Sheet1.

Employ the Range Observe method to determine the starting value of our rw variable.

In our case we have to use the ws.Cells.Find method, because nosotros are looking amongst the cells of Sheet1 that contains anything (What:="*").

Nosotros search by rows (SearchOrder:=xlRows) for the row containing the last (SearchDirection:=xlPrevious) value (LookIn:=xlValues).

Than we add 1 to this number to find the starting time empty row:

rw = ws.Cells.Discover(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

And from this point on our job is basically easy.

The written value in the field must exist equal with the rw variable. Nosotros store the number of the cells in the rw variable.

We tin can find out the names of our fields from the UserForm Properties window in case we did non use standard names for them.

Therefore, ws.Cells(rw, one).Value (this is the cell of Sheet1 first empty row in the start column) exist equal to Me.TextBox1.Value which is the value was given into our first field.

Me ever refers to the parent object that we "have" our lawmaking.

In this case information technology refers to UserForm, but if we was to write our code on a Sheet, that it would refer to that.

Create User-Feedback MessageBox

Here might be subservient to inform the user what happened to the input information, so with the help of the MsgBox command we can write a massage out for him.

At this fourth dimension on the popup massage window at that place should be only one OK button (vbOKOnly parameter) but of form with the help of the other parameters of the MsgBox we can tune this to our liking.

MsgBox "Stored!", vbOKOnly

If information technology is done we can delete the input values from our fields. Nosotros tin practice this because they are already in the appropriate cells.

The solution is easy:

We brand the value of the Me.TextBox1.Value equal to nothing.

Later on this is useful to bring back the cursor to the first field with the aid of the SetFocus parameter.

Error handling – How tin can we do this?

Basically nosotros take to effort to write the codes with minimal possible errors.

For this it is useful to insert an "If" branching so in example of empty cells the UserForm will do nothing. Our goal is to only store the data if all the cells / fields are filled out! The following brusk code will alert the user for insufficient information.

Just turn our thoughts to the code, namely

one. Put (If) after the removal of spaces (Trim)
two. Values of the first fields (Me.TextBox1.Value)
three. Equal to zip (= "")
iv. then (Then)
5. write a massage (MsgBox "Delight fill the field!"),
half-dozen. then exit (Leave Sub)
vii. and with this nosotros tin close the "If" branching (End If).

The VBA lawmaking:

What else is at that place left to practise? To determine how to start the UserForm.

Here we tin can set events or key-combinations also.

In our instance nosotros cull the simplest solution. We assign the startup of the code to a CommandButton.

Under the Ribbon Developer tab the Controls section tin can be found.

Let's insert a button with the help of the Insert command:

Past double clicking on the button in Desingn Fashion nosotros go over to the Visusal Bones Editor.

We merely need to show the UserForm named by the states DataEntryForm command for the "Click" upshot.

Here is the code snippet:

And nosotros are done!

After clicking on the button the UserForm opens. After filling this out the information gets into the advisable cells.

Conclusion:

Our today's information input showcase example was a very simplified task.

But information technology was perfect or u.s.a. to demonstrate: It is worth to utilize Excel for serious tasks and the VBA help u.s.a. to create interactive course for excel dashboard.

We hope you got into the fashion for VBA programming. We recommend that everyone start building their own little project!Download the VBA example!