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

Create macro

1. Open vb editor, press Alt+F11
2. Insert a new module

Step 1 - Find last nonempty cell in column B

We can find the last nonempty cell in column B with this code:

We want the first empty cell so we add one to the code.

This line of code saves the value in the variable Lrow. In the example given, the code saves the value 8 in Lrow.

Step 2 - Copy values

Let´s use the value in Lrow and copy values from cell B3:C3 to the first empty row.

Worksheets("Sheet1").Range("B" & Lrow & ":C" & Lrow) = Worksheets("Sheet1").Range("B3:C3").Value

becomes

Worksheets("Sheet1").Range("B8:C8") = Worksheets("Sheet1").Range("B3:C3").Value

Step 3 - Remove values

Worksheets("Sheet1").Range("B3:C3").Value = ""

Step 4 - Optimize code

Worksheets("Sheet1") is repeated many times. Let me show you how to optimize the code.

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

Step 5 - Create button

1. Go to developer tab
2. Click "Insert" button
3. Click "Button (Form Control)"
4. Create a button
5. Right click on your new button
6. Assign a macro
7. Select "AddText" macro

Final note:

There is a data entry form in excel but it is not on the ribbon.

Download excel file *.xlsm

Basic data entry.xlsm

This macro creates a new sheet and lists all tables and corresponding table headers in a workbook.

Sub ListTables()
Dim tbl As ListObject
Dim WS As Worksheet
Dim i, j As Single
Set WS = Sheets.Add
i = 1
For Each WS In Worksheets
    For Each tbl In WS.ListObjects
        Range("A1").Cells(i, 1).Value = tbl.Name
        For j = 1 To tbl.Range.Columns.Count
            Range("A1").Cells(i, j + 1).Value = tbl.Range.Cells(1, j)
        Next j
        i = i + 1
    Next tbl
Next WS
End Sub

Example

Sheet1, 2 and 3 contain three tables.

Run macro

1. Go to "Developer" tab
2. Click "Macros" button
3. Click "ListTables"
4. Click "Run"

Create macro

Open VB Editor, shortcut keys: Alt + F11.

Download excel 2007 macroenabled *.xlsm file

List all tables and headers in a workbook.xlsm

Rattan asks:

In my workbook I have three worksheets; "Customer", "Vendor" and "Payment".

In the Customer sheet I have a table, tblCustomer, where I add new customers. Similarly, in the Vendor sheet I have a table, tblVendor, where I add new vendors.

In the Payment sheet I have a table, tblPayment, where i have three columns; Date, Amount and Name.

Now, here is what I want to do; In the Name column of the tblPayment, I want to create a drop down list in each cell, which would contain all the names from tblCustomer[Name] and tblVendor[Name].

This way I can fill in the Date, Amount and then select one of all the names available in the drop down list of my Name cell. Is this possible without using VB code or any macro? If so, please help me out with this.

Answer:

Yes it is possible without using vba.

Sheet: Customer

Sheet: Vendor

Sheet:Calc

Array formula in cell A2:

How to create an array formula

1. Copy (Ctrl + c) above array formula
2. Select cell A2
3. Paste (Ctrl + v)
4. Press and hold Ctrl + Shift
5. Press Enter once
6. Release all keys

How to copy array formula

1. Select cell A2
2. Copy (Ctrl + c)
3. Select cell range A3:A100
4. Paste (Ctrl + v)

Create named range

Named range formula:

Sheet: Payment

Create drop down list

1. Select cell C2
2. Go to "Data" tab
3. Click "Data validation"
   
4. Select List
5. Type =Names in Source field
6. Click OK

Download excel file *.xlsx

Basic invoice template.xlsx

Deeks asks:

need to truncate the undermentioned column to create columns of weeks it has, for eg. 1/26/2011 was a wed, so need a column for 1/26/2011-sun 1/29/2011, and then onwards each week starting monday till 8/31/2011. please help asap

Assigned Dates
1/26/2011 - 8/31/2011
2/1/2011 - 3/30/2011
2/1/2011 - 3/30/2011
2/1/2011 - 3/30/2011
11/1/2010 - 2/11/2011
1/26/2011 - 8/31/2011

Answer:

Array Formula in cell  C2:

How to create an array formula

1. Select cell C2
2. Copy above array formula
3. Paste into formula bar
4. Press and hold Ctrl + Shift
5. Press Enter once
6. Release all keys

How to copy array formula

1. Select cell C2
2. Copy (Ctrl + c)
3. Select cell range C3:AH3
4. Paste (Ctrl + v)

This blog post demonstrates how to create, populate and change comboboxes (form control) programatically.

Form controls are not as flexible as ActiveX controls but are compatible with earlier versions of Excel. You can find the controls on the developer tab. How to show the developer tab

Table of Contents

Create a combobox using vba

Assign a macro - Change event

Add values to a combobox

Remove values from a combo box

Set the selected value in a combo box

Read selected value

Link selected value

Change combobox properties

Populate combox with values from a dynamic named range

Populate combox with values from a table

Currently selected combobox

Create a combobox using vba

Sub CreateFormControl()
'Worksheets("Sheet1").DropDowns.Add(Left, Top, Width, Height)
Worksheets("Sheet1").DropDowns.Add(0, 0, 100, 15).Name = "Combo Box 1"
End Sub

Back to top
Assign a macro - Change event

You can assign a macro to a combobox by right clicking on the combobox and select "Assign Macro". Select a macro in the list and press ok!

This subroutine does the same thing, it assigns a macro named "Macro1" to "Combo box 1" on sheet 1. Macro1 is executed as soon as the selected value in the combo box is changed.

Sub AssignMacro()
Worksheets("Sheet1").Shapes("Combo Box 1").OnAction = "Macro1"
End Sub

Back to top
Add values to a combo box
You can add an array of values to a combo box. The list method sets the text entries in a combo box, as an array of strings.

Sub PopulateCombobox1()
Worksheets("Sheet1").Shapes("Combo Box 1").ControlFormat.List = _ 
Worksheets("Sheet1").Range("E1:E3").Value
End Sub

The difference with the ListFillRange property is that the combo box is automatically updated as soon as a value changes in the assigned range. You don´t need to use events or named ranges to automatically refresh the combo box, except if the cell range also changes in size.

Sub PopulateCombobox2()
Worksheets("Sheet1").Shapes("Combo Box 1").ControlFormat.ListFillRange = _
"A1:A3"
End Sub

You can also add values one by one.

Sub PopulateCombobox3()
With Worksheets("Sheet1").Shapes("Combo Box 1").ControlFormat
.AddItem "Sun"
.AddItem "Moon"
.AddItem "Stars"
End Sub

Back to top
Remove values from a combo box

RemoveAllItems is self-explanatory.

Sub RemoveAllItems()
Worksheets("Sheet1").Shapes("Combo Box 1").ControlFormat.RemoveAllItems
End Sub

The RemoveItem method removes a value using a index number.

Sub RemoveItem()
Worksheets("Sheet1").Shapes("Combo Box 1").ControlFormat.RemoveItem 1
End Sub

The first value in the array is removed.
Back to top
Set the selected value in a combo box
The ListIndex property sets the currently selected item using an index number. ListIndex = 1 sets the first value in the array.

Sub ChangeSelectedValue()
With Worksheets("Sheet1").Shapes("Combo Box 1")
.List = Array("Apples", "Androids", "Windows")
.ListIndex = 1
End With
End Sub

Back to top
Read selected value

The ListIndex property can also return the index number of the currently selected item. The list method can also return a value from an array of values, in a combo box. List and Listindex combined returns the selected value.

Sub SelectedValue()
With Worksheets("Sheet1").Shapes("Combo Box 1").ControlFormat
MsgBox "ListIndex: " & .ListIndex & vbnewline & "List value:" .List(.ListIndex)
End With
End Sub

Back to top
Link selected value

Cell F1 returns the index number of the selected value in combo box 1.

Sub LinkCell()
Worksheets("Sheet1").Shapes("Combo Box 1").ControlFormat.LinkedCell = "F1"
End Sub

Cell G1 returns the selected value in combo box 1.

Sub LinkCell2()
With Worksheets("Sheet1").Shapes("Combo Box 1").ControlFormat
    Worksheets("Sheet1").Range("G1").Value = .List(.ListIndex)
End With
End Sub

Back to top
Change combobox properties
Change the number of combo box drop down lines.

Sub ChangeProperties()
Worksheets("Sheet1").Shapes("Combo Box 1").ControlFormat.DropDownLines = 2
End Sub

Back to top
Populate combox with values from a dynamic named range

I created a dynamic named range Rng, the above picture shows how.

Sub PopulateFromDynamicNamedRange()
Worksheets("Sheet1").Shapes("Combo Box 1").ControlFormat.ListFillRange = "Rng"
End Sub

You can do the same thing as the subroutine accomplishes by right clicking the combo box and then left click "Format Control...". Input Range: Rng
Back to top
Populate combox with values from a table

Sub PopulateFromTable()
Worksheets("Sheet1").Shapes("Combo Box 1").ControlFormat.List = _
[Table1[DESC]]
End Sub

Back to top
Currently selected combobox

It is quite useful to know which combo box the user is currently working with. The subroutine below is assigned to Combo Box 1 and is executed when a value is selected. Application.Caller returns the name of the current combo box.

Sub CurrentCombo()
MsgBox Application.Caller
End Sub

Back to top