Author: Oscar Cronquist Article last updated on January 08, 2019

This post demonstrates how to view saved invoices based on the invoice number using a userform. The userform appears when the user clicks 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. Right-click on Userform1.
  3. Click "View Code" and the code module will appear.
  4. Copy and paste "Sub Button1_Click" and "CommandButton2_Click" code below to code module.
'The following macro will remove the userform if visible to the user
Private Sub CommandButton1_Click()
  Unload UserForm1
End Sub

"View Invoice data" Command button

Copy and paste "Sub Button2_Click" code below to code module

Private Sub CommandButton2_Click()
'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

Download Excel file


Populate-invoice-listbox-part-2.xls

Related articles

Save invoice data [VBA]

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

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 [โ€ฆ]

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 [โ€ฆ]