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, _
RowEnd = Sheets("Invoice data").Columns("A").Find(ListBox1.Value, _
SearchOrder:=xlRows, LookAt:=xlWhole, SearchDirection:=xlPrevious, _  
LookIn:=xlValues).Row + 1
'Clear range

'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