+ Reply to Thread
Results 1 to 9 of 9

ListBox Array creates blank ListBox item

  1. #1
    Forum Contributor onmyway's Avatar
    Join Date
    09-03-2012
    Location
    Johannesburg, South Africa
    MS-Off Ver
    2013
    Posts
    386

    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!
    Attached Files Attached Files
    Last edited by onmyway; 05-28-2015 at 07:48 AM.
    Sharing knowledge, can be likened to taking another person's hand, and pulling them up to a higher level -- onmyway

    If I was helpful, please remember to click on * Add Reputation below

  2. #2
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    861

    Re: ListBox Array creates blank ListBox item

    that .AddItem in the cmdAdd_Click() sub is adding an extra row to listbox. Try to comment it and give it a shot.
    .. and don't forget to have fun!
    Bogdan.

    mark SOLVED and Add Reputation if my answer pleases you

  3. #3
    Forum Contributor onmyway's Avatar
    Join Date
    09-03-2012
    Location
    Johannesburg, South Africa
    MS-Off Ver
    2013
    Posts
    386

    Re: ListBox Array creates blank ListBox item

    hi bulina2k,

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

  4. #4
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    861

    Re: ListBox Array creates blank ListBox item

    the blank line is from the userform initialize
    Please Login or Register  to view this content.

    here it populates listbox first row with blanks

  5. #5
    Forum Contributor onmyway's Avatar
    Join Date
    09-03-2012
    Location
    Johannesburg, South Africa
    MS-Off Ver
    2013
    Posts
    386

    Re: ListBox Array creates blank ListBox item

    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.

  6. #6
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    861

    Re: ListBox Array creates blank ListBox item

    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..

  7. #7
    Forum Contributor onmyway's Avatar
    Join Date
    09-03-2012
    Location
    Johannesburg, South Africa
    MS-Off Ver
    2013
    Posts
    386

    Re: ListBox Array creates blank ListBox item

    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?

  8. #8
    Forum Contributor onmyway's Avatar
    Join Date
    09-03-2012
    Location
    Johannesburg, South Africa
    MS-Off Ver
    2013
    Posts
    386

    Re: ListBox Array creates blank ListBox item

    Thank you bulina2k

    Your suggestion worked well for a workaround for the problem!

  9. #9
    Registered User
    Join Date
    02-18-2018
    Location
    Egypt
    MS-Off Ver
    2013
    Posts
    23

    Re: ListBox Array creates blank ListBox item

    Dear onmyway

    hello

    did you finish this file ?

    i need it urgent

    thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] if anything in listbox select first item, if listbox empty do nothing (listbox in userform
    By mcdermott2 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-17-2015, 12:49 PM
  2. Select one item from a listbox disables another listbox
    By EagleInsight in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-22-2014, 09:53 PM
  3. [SOLVED] first item in listbox does not show in listbox
    By cfinch100 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-19-2013, 10:33 AM
  4. [SOLVED] Copy one listbox item to same index position in another listbox
    By Foreverlearning in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-19-2012, 06:05 AM
  5. Populate ListBox Based On Item Chosen In Another ListBox
    By davemojo82 in forum Excel General
    Replies: 1
    Last Post: 08-04-2009, 08:39 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1