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