+ Reply to Thread
Results 1 to 4 of 4

Values associated with listbox items when blanks are skipped

  1. #1
    Registered User
    Join Date
    10-17-2014
    Location
    North Carolina
    MS-Off Ver
    2010
    Posts
    2

    Values associated with listbox items when blanks are skipped

    I have a userform with ListBox1 that is populated via looping excluding blanks cells from a column in Sheet1.
    The length of the list on Sheet1 will vary.
    For example; I may have a list of 100 entries on Sheet1 in columnA but only show 10 entries in ListBox1 if 90 of those entries were blank.

    Once ListBox1 opens up and allows the user to select multiple entries and clicks an "OK" command button, I want to be able to flag those entries on the corresponding 100 entry list on Sheet1, "1" if select or "0" is not.
    I cannot use the listcount or index b/c the listbox only has 10 items in it and loses it's original item # among the list of 100. In the list of 10 the entry could be the first entry but is the 25 entry in the list of 100.

    Any ideas how to accomplish this?

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Values associated with listbox items when blanks are skipped

    Hello Koshimura,

    Add a second column to list box and hide it. In this column you can add the cell address of the item you added to the list.

    When the user makes a selection, you can access the the cell address for the selected item and highlight the row or whatever you want to do.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    10-17-2014
    Location
    North Carolina
    MS-Off Ver
    2010
    Posts
    2

    Re: Values associated with listbox items when blanks are skipped

    Good Idea.

    If I decide to include a listbox with 4 columns and populate the first 3 columns with data from 3 columns in Sheet1, how do I hide only the 4th column which would include the cell address or the original item # count?
    I am unfamiliar with multiple column listboxes.

    Thanks!

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Values associated with listbox items when blanks are skipped

    Hello Koshimura,

    You can control twhich columns are visible by using the ColumnWidths property. Setting any column to 0 9zero) will hide it.

    For example, ListBox1.ColumnWidths = "60;60;60;0" makes the first three columns 60 points wide and the fourth not visible.

+ 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. Textbox to show values when clicked items on listbox
    By Benjamin2008 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-11-2013, 07:02 PM
  2. [SOLVED] Auto select items in a Listbox with items from another Listbox
    By perducci in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 05-22-2013, 04:45 PM
  3. [SOLVED] VBA: transfer values of textboxes & items from listbox to worksheet
    By hydz1213 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-19-2013, 05:46 AM
  4. Transfer random listbox items to new listbox and then loop through selected items
    By narrowgate88 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-31-2013, 05:58 PM
  5. Look at values in listbox not the number of items
    By Buddy7 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-18-2011, 02:41 PM

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