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
Invoice category
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
This article demonstrates a basic invoice template I created. It lets you use dropdown lists to quickly select products on […]
This post demonstrates how to: Insert a button to your worksheet Assign a macro to the button Create a basic […]
In a previos post:Excel vba: Save invoice data we added/copied data between sheets. This post describes how to overwrite existing […]
Rattan asks: In my workbook I have three worksheets; "Customer", "Vendor" and "Payment". In the Customer sheet I have a […]
Question: I have a long table The key is actually col B&C BUT…sometime there are few rows with same key […]
Macro category
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
In this blog article, I will demonstrate basic file copying techniques using VBA (Visual Basic for Applications). I will also […]
Excel does not resize columns as you type by default as the image above demonstrates. You can easily resize all […]
This article describes how to create an interactive chart, the user may press with left mouse button on a button […]
Rahul asks: I want to know how to create a vlookup sheet, and when we enter a name in a […]
This article describes how to create a button and place it on an Excel worksheet then assign a macro to […]
Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]
This article demonstrates a macro that inserts new worksheets based on names in a cell range. The cell range may […]
In this article, I am going to demonstrate a simple workbook where you can create or delete projects and add […]
Today I would like to share with you these small event handler procedures that make it easier for you to […]
This article explains how to hide a specific image in Excel using a shape as a button. If the user […]
This article describes different ways to locate literal or hardcoded values in formulas. The image above shows the result from […]
This article demonstrates macros that save worksheets to a single pdf file. What's on this webpage Export all worksheets in […]
Excel categories
4 Responses to “Select and view invoice [VBA]”
Leave a 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.
Thanks for this useful file, is there a file with statement of account, show invoice and payment.
Excel 2016
Run-time error '457'
How Fixing code
Test.Add Value, CStr(Value)
Excel 2016
Run-time error '457'
How Fixing code
Test.Add Value, CStr(Value)
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