Basic data entry [VBA]
In this small tutorial, I am going to show you how to create basic data entry with a small amount of VBA code.
Cell B3 and C3 are input cells. When you press with left mouse button on button "Add", the data in cell B3 and C3 are copied to the first empty row in the list.
The last step in the code removes the values in cell B3 and C3, see the animated image above.
Macro
Sub AddText()
Dim Lrow As Single
With Worksheets("Sheet1")
Lrow = .Range("B" & Rows.Count).End(xlUp).Row + 1
.Range("B" & Lrow & ":C" & Lrow) = .Range("B3:C3").Value
.Range("B3:C3").Value = ""
End With
End Sub
Add macro to your workbook
- Press Alt+F11 to open the VB Editor.
- Press with right mouse button on on your workbook name in the Project Explorer window.
- Press with left mouse button on "Insert".
- Press with left mouse button on "Module" to insert a code module to your workbook.
- Copy macro and paste to code module, see image above.
Explaining macro
Step 1 - Name macro
Sub AddText()
Step 2 - Declare variables and data types
Dim Lrow As Single
Step 3 - Find last non-empty cell in column B
This line returns the row number of the last non-empty cell in column B:
We want the first empty cell so we add one to the code.
This line of code saves the value to variable Lrow. In the example given above, the code saves the value 8 to Lrow.
Step 4 - Copy values
Let's use the value in Lrow and copy values from cell B3:C3 to the first empty row.
Step 5 - Remove values
The following line deletes the values in cell range B3:C3.
Step 6 - End macro
Simplify code
Worksheets("Sheet1") is repeated many times. The WITH and WITH END statement allows you to simplify the code.
Dim Lrow As Single
With Worksheets("Sheet1")
Lrow = .Range("B" & Rows.Count).End(xlUp).Row + 1
.Range("B" & Lrow & ":C" & Lrow) = .Range("B3:C3").Value
.Range("B3:C3").Value = ""
End With
End Sub
Create button
- Go to the Developer tab.
- Press with left mouse button on "Insert" button on the ribbon.
- Press with left mouse button on "Button" (Form Control).
- Press and hold with left mouse button on the worksheet.
- Drag with mouse to create the button then release the mouse button.
Assign macro
- Press with right mouse button on on your new button.
- Assign a macro.
- Select "AddText" macro.
Final note
There is a data entry form in Excel but it is not on the ribbon.
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
Open Excel files in a folder [VBA]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
Split data across multiple sheets [VBA]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
Identify missing numbers in a column
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]
Working with COMBO BOXES [Form Controls]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
Change chart data range using a Drop Down List [VBA]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
Run a Macro from a Drop Down list [VBA]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]
What's on this page Copy a file Copy and rename a file Rename a file List files in a folder […]
This article describes how to create a button and place it on an Excel worksheet then assign a macro to […]
6 Responses to “Basic data entry [VBA]”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Could you please show me the code to place the copied data into a different tab instead of below the input cells. Its annoying me and I'm rubbish at this. thanks.
phil,
see this post:
Add values to different sheets (vba)
[...] a previous post I described how to simplifiy data entry. Now it is time to put values in separate [...]
Hi,
First of all thanks its a very great Macro.
But i would like to do a little modification.
What should i change, to copy the values, to the first row, and pushing down the other datas, and not after the last one?
i guess somewhere here:
.Range("B" & Lrow & ":C" & Lrow) = .Range("B3:C3").Value
Thanks:
Márton
you need to for i loop
Hi Oscar,
first of all thank you for all the awesome and detailed tutorials.
I would like to add data to a table, using the method you describe. How can I, using vba, add an empty row at the end of the table and copy the new data there?
Thank you