ListBox Array creates blank ListBox item

Hi guys,

i hope you can help me.

I have a UserForm_Initialize event that creates an array for my ListBox1 to handle more than 10 columns. This works beautifully, except that this creates a single "blank" line in my ListBox, which in turn creates an empty line in my DB when saving my data. This empty/blank item in my ListBox is really problematic, and can cause other serious repercussions.

Detail: I have a UserForm used for capturing something like an invoice. You have the record details captured at the top (name, surname etc.), and then a ListBox used for capturing multiple "line items".

This is the UserForm_Initialize code used for setting up my ListBox:

Please Login or Register  to view this content.

This section of code is included in both the userForm_Initialize and cmdAdd events, but should only be in the cmdAdd event. I ONLY included it at both places to correctly calculate the GlobalData and references:

Please Login or Register  to view this content.

This is the cmdAdd button code for saving a line item to the ListBox:

Please Login or Register  to view this content.

I have tried MANY possible and promising solutions, but cannot seem to get this to work with my limited knowledge. My solutions create other situations where my lines of data are overridden, or I get various errors.

Please see attached sample workbook.

Please Note: This is only a quick example I have drawn up for this purpose, so please forgive me for my control names etc.

Thank you in advance!

that .AddItem in the cmdAdd_Click() sub is adding an extra row to listbox. Try to comment it and give it a shot.

hi bulina2k,

The issue is that after the userForm initializes, there is a blank line in the ListBox already. Before a run the cmdAdd....

the blank line is from the userform initialize

Please Login or Register  to view this content.

here it populates listbox first row with blanks

Hi bulina2k,

Thank you.

However, without that line my code does not work. I then get an invalid property array index error 381.

I am trying to get a way of making use of that first blank line (populate it with my firs line), and then all additional lines are created in sequence. Here it gets a bit hazy...

I have tried variables to replace the .ListCount in this code, but it always bombs out after the first line.

Please Login or Register  to view this content.

the workaround for this might be to reduce the for loop to

Please Login or Register  to view this content.

in the Private Sub btnSave_Click()

tell me what you think..

Hi bulina2k,

This indeed seems to do the trick for not saving the blank row. I will do some more testing and revert.

Something to think about; I was hoping I could populate that first empty line - any ideas?

Thank you bulina2k

Your suggestion worked well for a workaround for the problem!