This post demonstrates how to view saved invoices based on the invoice number using a userform. The userform appears when the user press with left mouse button ons on button "View Invoice", see image above.

The userform contains:

• Listbox populated with unique distinct invoice numbers.
• A "Cancel" button that unloads the userform.
• A button "View Invoice data" that will start the macro. The macro will extract the invoice data from another sheet based on the selected invoice number in the listbox.

Cancel Command button

1. Press Alt-F11 to open the visual basic editor.
2. Press with right mouse button on on Userform1.
3. Press with left mouse button on "View Code" and the code module will appear.
4. Copy and paste "Sub Button1_Press with left mouse button on" and "CommandButton2_Press with left mouse button on" code below to code module.

'The following macro will remove the userform if visible to the user
Private Sub CommandButton1_Press with left mouse button on()
  Unload UserForm1
End Sub

"View Invoice data" Command button

Copy and paste "Sub Button2_Press with left mouse button on" code below to code module

Private Sub CommandButton2_Press with left mouse button on()
'Declare variables
Dim RowStart As Long
Dim RowEnd As Long
'Find invoice range
RowStart = Sheets("Invoice data").Columns("A").Find(ListBox1.Value, _
SearchOrder:=xlRows, LookAt:=xlWhole, SearchDirection:=xlNext, _
LookIn:=xlValues).Row  
RowEnd = Sheets("Invoice data").Columns("A").Find(ListBox1.Value, _
SearchOrder:=xlRows, LookAt:=xlWhole, SearchDirection:=xlPrevious, _  
LookIn:=xlValues).Row + 1
'Clear range
Sheets("Invoice").Range("B16:E38").ClearContents

'Copy values from sheet Invoice data
Sheets("Invoice").Range("B16").Resize(RowEnd - RowStart, 4).Value = _
Sheets("Invoice data").Range("D" & RowStart & ":G" & RowEnd).Value
Sheets("Invoice").Range("F3").Value = Sheets("Invoice data").Range("B" _
& RowStart).ValueSheets("Invoice").Range("D13").Value = _
Sheets("Invoice data").Range("A" & RowStart)
Sheets("Invoice").Range("B8").Value = Sheets("Invoice data").Range("C" _
& RowStart)
'Hide the userform
Unload UserForm1
End Sub

Get the Excel file

Populate-invoice-listbox-part-2.xls

Invoice category

Save invoice data [VBA]
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]

Invoice template with dependent drop down lists
This article demonstrates a basic invoice template I created. It lets you use dropdown lists to quickly select products on […]

Populate listbox with unique distinct values [VBA]
This post demonstrates how to: Insert a button to your worksheet Assign a macro to the button Create a basic […]

Macro category

Save invoice data [VBA]
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 […]

Excel categories

Comments (4)