Select and view invoice [VBA]

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

Press Alt-F11 to open the visual basic editor.
Press with right mouse button on on Userform1.
Press with left mouse button on "View Code" and the code module will appear.
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 […]

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

Basic invoice template
Rattan asks: In my workbook I have three worksheets; "Customer", "Vendor" and "Payment". In the Customer sheet I have a […]

Sum unique distinct invoices
Question: I have a long table The key is actually col B&C BUT…sometime there are few rows with same key […]

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

Working with COMBO BOXES [Form Controls]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]

Identify missing numbers in a column
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]

Run a Macro from a Drop Down list [VBA]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]

Excel calendar [VBA]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]

Change chart data range using a Drop Down List [VBA]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]

Working with FILES
What's on this page Copy a file Copy and rename a file Rename a file List files in a folder […]

Auto resize columns as you type
Excel does not resize columns as you type by default as the image above demonstrates. You can easily resize all […]

How to create an interactive Excel chart [VBA]
This article describes how to create an interactive chart, the user may press with left mouse button on a button […]

How to change a picture in a worksheet dynamically [VBA]
Rahul asks: I want to know how to create a vlookup sheet, and when we enter a name in a […]

Create a Print button [VBA]
This article describes how to create a button and place it on an Excel worksheet then assign a macro to […]

Create a unique distinct list using Advanced Filter in a macro [VBA]
Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]

Create new worksheets programmatically based on values in a cell range [VBA]
This article demonstrates a macro that inserts new worksheets based on names in a cell range. The cell range may […]

Excel template: Getting Things Done [VBA]
In this article, I am going to demonstrate a simple workbook where you can create or delete projects and add […]

How to highlight row of the selected cell programmatically
Today I would like to share with you these small event handler procedures that make it easier for you to […]

Show / hide a picture using a button
This article explains how to hide a specific image in Excel using a shape as a button. If the user […]

Find cells containing formulas with literal (hardcoded) values
This article describes different ways to locate literal or hardcoded values in formulas. The image above shows the result from […]

How to save specific multiple worksheets to a pdf file programmatically
This article demonstrates macros that save worksheets to a single pdf file. What's on this webpage Export all worksheets in […]

More than 1300 Excel formulas

Excel categories

Home page

Latest updated articles.

Excel Functions

More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.

Excel Formulas

More than 1300 formulas organized in subcategories.

Excel Tables

Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.

Advanced Filter

Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.

Data Validation

Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.

Drop Down List

Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.

Named Ranges

Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.

Excel Solver

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.

Charts

An Excel feature that lets you visualize data in a graph.

Conditonal Formatting

Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.

Pivot Tables

Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA

VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

Macros

A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.

UDFs

UDF stands for User Defined Functions and is custom built functions anyone can create.

4 Responses to “Select and view invoice [VBA]”

mohamed says:

February 12, 2015 at 9:24 am

Thanks for this useful file, is there a file with statement of account, show invoice and payment.

Reply
be says:

November 11, 2016 at 7:24 pm

Excel 2016

Run-time error '457'

How Fixing code
Test.Add Value, CStr(Value)

Reply
be says:

November 11, 2016 at 7:27 pm

Excel 2016
Run-time error '457'

How Fixing code

Test.Add Value, CStr(Value)

Reply
maria says:

December 17, 2017 at 1:21 pm

hi. in select and view invoice with listbox,when i clear content range D7:I16 but do not remove formulas from rang D7:I16, i use this code:
'Clear range

Set rConstants = Sheets("Invoice Form").Range("D7:I16").SpecialCells(xlCellTypeConstants)
rConstants.ClearContents

but this code remove formulas
pls help me

Reply

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form