build a data entry page in visual basic for excel

#Build a data entry page in visual basic for excel how to
#Build a data entry page in visual basic for excel full
#Build a data entry page in visual basic for excel code

#Build a data entry page in visual basic for excel code

This piece of code will find that row and store it into a variable named next_row:

Now, we need to find out what the next empty row on the data worksheet is so that we don't overwrite anything.

(Since we gave a unique name to all of our input fields in Step 8 of the first section of this tutorial, we do not need to store the name of the data Input worksheet.) The variable ws_output stores the text Data, which is just the name of the worksheet where the data will go. This is something that you don't have to do but it will help a lot if you ever change the name of the worksheets.

To start the macro, I store the name of the worksheet where the input will be stored, the Data worksheet.

Hit Enter and it will automatically be formatted like this:

Type sub then a space and then the name you want to give the macro.

You also need to add a title row for your data in order for the macro I make here to work. I created a worksheet named Data and put a title for each column that will hold data. I'll be covering the last option here, so the first step is to create the worksheet and make sure we know where the data will go. You can have it emailed somewhere, inserted into a real database, or simply stored on a separate worksheet.

Figure out where you will put the data.

#Build a data entry page in visual basic for excel how to

Here, I will show you how to make a macro, but it will be easy to follow, I promise. Now that you have a form, let's make that Submit button work. You can also lock the worksheet and leave only the 4 white input fields unlocked if you want to protect the form and you can use Data Validation to make sure the user inputs the correct type of data. Resize the window and hide the ribbon menu (you can use a macro to completely hide the ribbon menu if you want).Uncheck Formula bar, Gridlines, and Headings. To finish the form's appearance, go to the View tab and look in the Show section.Repeat this for the other fields until each one has a unique name. Once you type the name, you must hit Enter. Select the white input field for first name and then look to the white box just above where it says column A, that is the Name Box and that is where we will type a name for this field I named mine first_name. This will make creating the macro much easier. Before we continue, I want to add a name to each input field.Hit the Esc key when finished and then go to the Home tab and change the formatting of the text like you would regular text in a cell. Make sure the button is selected and then go to the Format tab that will appear and click the bottom arrow in the Shape Styles section and select a pre-made one from there if you want.Ĭlick the button again and type some text. Then, left-click on the mouse, hold, and drag until you have the desired size for the button. Go to the Insert tab and click Shapes and select a Rounded Rectangle from the list.

#Build a data entry page in visual basic for excel full

If you want, you can add a full border around each white cell some people like that.

Select the cells in column C that are in the same row as the form field titles and make their background white.

Click the square in the upper left of the rows and columns or just hit Ctrl + A to select everything.Ĭlick the paint bucket in the Font box on the Home tab and select a grayish color for the background.Then make column B small we will use this column as a separator between the form input field and title. Make column A just big enough to show the titles for the form fields.

Then, change the height of one and all will change at the same time.

Adjust the spacing between each field by selecting the empty row between them while holding the Ctrl button.

This allows you to control the spacing between the fields and that can make it easier to use.Īlso apply some basic formatting to the title I just make them bold.

Skip a row and then input the form field's titles.

Create a title and give it a title-like format:.

Here are some steps for a basic form that I like. You can spend a lot of time or a little doing this but the most important thing is that it is easy for the user to understand what to do. Sections:įirst, we create the form where the user will input the data. This tutorial does use a macro to get the data from the input form to the worksheet where it will be stored but don't worry, I will cover that step-by-step. This method uses a simple-to-create input form that anyone can make and maintain within the worksheet and does not cover UserForms that are made in VBA and that can be quite tricky for users to create and maintain. How to make a user input form in Excel and have the data stored on another worksheet at the click of a button.