ListBox Code for VBA form

If you're populating the listbox from the invoices on your MasterData sheet, then you could use the index number of the selected item in the listbox to determine the row number. The listbox index numbers are zero-based. If you populate the listbox starting with row 2 on MasterData, then the .ListIndex property + 2 will return the row number of the selected item in the listbox.

rownum = ListBox1.ListIndex + 2

Just make sure you repopulate the listbox whenever you make a change to the sheet.

Hi,

The way I'd do this is create a dynamic range name for your invoice list. e.g.

lst_inv :

Formula:

 =OFFSET(MasterData!$A$1,0,0,COUNTA(MasterData!$B:$B),7)

change the 7 to change the number of columns you see in the ListBox

Set the RowSource property of the Listbox to lst_inv
Set the ColumnWidth Property to say
30 pt;60 pt;60 pt;60 pt;60 pt;60 pt - change the numbers as necessary to display the number of characters from each field.

Use the

Private Sub FindButton_Click()
Me.InvoiceList.RowSource = Range("inv_list")
End Sub

procedure to list the invoices.

That's a starter for 10.

I'd then use the click event of the listbox to identify the row that's been selected and use that that row number to index the row in the Master Data sheet that contains the details and populate the form fields with the data from that row.


See attached

My 2cents.
Setting the Columnwidth of a certain Column to zero will not display that Column.

Cheers Rudi!

I'm trying to use the following code for the 'Delete' button but it only works if the rows have no data stored.

Private Sub DeleteButton_Click()
    
    'Delete Invoice
    
        Selection.EntireRow.Delete
                
End Sub

The 'Amend' button I'm trying to code so that the data can be updated rather than duplicate or add a new row/invoice.

Any ideas?

Je bent zeer vriendelijk
Davo

Select a row in ListBox and click Delete-button.

Private Sub DeleteButton_Click()

    'Delete Invoice
    Dim sn
    With Sheets("MasterData")
        .Cells(InvoiceList.ListIndex + 2, 1).EntireRow.Delete
        sn = .Cells(1).CurrentRegion.Offset(1).Value
    End With
    With InvoiceList
        .ColumnCount = UBound(sn, 2)
        .List = sn
    End With
        
End Sub

This should do it.