ListBox Code for VBA form

**davo3286** · 07-10-2018, 05:15 AM

I'm currently creating an invoicing system using a VBA form which is going really well however, I am now stuck with what I am hoping to achieve next.

Everything is working great in terms of adding data to the form and using the 'Add' command button to store the data on a worksheet, but I am now trying to implement the next function...

1. A ListBox to then be able to use the 'Find', 'Amend' 'Delete' functions etc.

My idea for this is to display all invoices in the ListBox at the bottom of the form with a scroll bar on the right hand side.

I would like to then be able to click on whichever invoice necessary and then have the data displayed on the form for me to make amends/changes or even delete a row if needed (I already have the code for the Delete ComandButton but obviously this won't work until the form can load data back into the form fields so it knows which row is to be dleted).

I'm only asking for a starting point or recommended tutorials

Cheers!

**AlphaFrog** · 07-10-2018, 05:54 AM

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.

**Richard Buttrey** · 07-10-2018, 05:55 AM

Hi,

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

lst_inv :

Formula:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

**davo3286** · 07-10-2018, 07:00 AM

Thanks both!,

Richard, I really like the idea of using the dynamic range name for the ListBox, it works well.

1. Do you know if it's possible to choose which of the columns display in the listBox or is it a case of it will always be A1 working its way along the columns

2. Is it possible to make the top row of the ListBox Bold where the titles are so they stand out? I've tried to mess around with it but was all bold or all regular from what I was able to do.

Cheers
Davo

**bakerman2** · 07-10-2018, 07:23 AM

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

**davo3286** · 07-10-2018, 08:40 AM

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.

Please Login or Register  to view this content.

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

**bakerman2** · 07-10-2018, 09:17 AM

Select a row in ListBox and click Delete-button.

Please Login or Register  to view this content.

**davo3286** · 07-10-2018, 09:56 AM

Thanks as always Rudi,

Anyone got an idea of how the 'Amend' button I'm trying to code would work cz at the moment the data is duplicating rather than just updating that row...

**bakerman2** · 07-10-2018, 05:52 PM

This should do it.

**davo3286** · 07-11-2018, 06:47 AM

Thanks Rudi,

I wouldn't be anywhere near finished without your help with the code... much appreciated!

1. Is it possible to use the form as the actual invoice or will it be better exported from the worksheet like I'm currently planning?

I just though maybe I could use the form as a 'programme' use a 'process invoice' command button with only the fields relevant to the invoice exported. What's your thoughts?

Cheers
Davo

ListBox Code for VBA form

LinkBack

Thread Tools

Rate This Thread

Display

ListBox Code for VBA form

Re: ListBox Code for VBA form

Re: ListBox Code for VBA form

Re: ListBox Code for VBA form

Re: ListBox Code for VBA form

Re: ListBox Code for VBA form

Re: ListBox Code for VBA form

Re: ListBox Code for VBA form

Re: ListBox Code for VBA form

Re: ListBox Code for VBA form

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] VBA code to change a chart based on a selection from a listbox on a form

User Form ListBox - Find Records (ListBox Populating Issue)

Is it possible to do that using a Listbox Form?

Form Control Listbox VBA

Modification of listbox to listbox code

listbox value to a form

[SOLVED] vb6 form with listbox

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions