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

---

Weekly Blog EMAIL

[newsletter_signup_form id=1]

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

---

Edit invoice data [VBA]

In a previos post:Excel vba: Save invoice data we added/copied data between sheets. This post describes how to overwrite existing […]

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 […]

Return multiple matches with wildcard vlookup

Mr.Excel had a "challenge of the month" June/July 2008 about Wildcard VLOOKUP: "The data in column A contains a series […]

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 […]

List Excel defined Tables in a workbook [VBA]

The following macro inserts a new sheet to your workbook and lists all Excel defined Tables and corresponding Table headers […]

Save invoice data [VBA]

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