+ Reply to Thread
Results 1 to 7 of 7

Highlight visible items of ListBox based on a condition

  1. #1
    Registered User
    Join Date
    11-04-2014
    Location
    Portugal
    MS-Off Ver
    XL2013/XL365
    Posts
    83

    Highlight visible items of ListBox based on a condition

    Good afternoon

    I have a problem that I don't know if it can be solved, but here is for sure the best place to know, this is:

    - I have a ListBox with several columns, in the example that attachment I just put with three but may be 14, where I only want to show certain columns, in this case only show the first two columns, and only show 12 ListBox items at a time;
    - That is the Height, Width and number of visible items (ListBox lines) are fixed - in this case there are 12.

    The intention was that whenever the value of the third column was different from "", ie whenever there is value, the color of the label, which is now red, would change to yellow (or any other color), without "Clicking" in the ListBox, ie when I first show the ListBox, the visible ListBox items correspond to ListIndex = 0 to ListIndex = 11; but as I scroll up or down the visible items vary as do the visible ListIndex.

    Is it possible to do what I want?
    How is it possible? (Edited / Added later)

    Thanks
    Jorge Cabral
    Attached Files Attached Files
    Last edited by JCabral; 10-18-2019 at 08:20 AM.

  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: Highlight visible items of ListBox based on a condition

    Hello JCabral,

    To answer your question, yes it can be done. The ListBox scroll feature does not have any exposed events. To access the scrolling would require Subclassing the ListBox's window procedure using the Windows API so your code can "see" the event first before any other system code.

  3. #3
    Registered User
    Join Date
    11-04-2014
    Location
    Portugal
    MS-Off Ver
    XL2013/XL365
    Posts
    83

    Re: Highlight visible items of ListBox based on a condition

    Hi Leith Ross

    Can you help with some examples, so i can apply to my case?
    Please make in consideration that i am a rookie in VBA

    Many thanks
    Jorge Cabral

  4. #4
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    3,258

    Re: Highlight visible items of ListBox based on a condition

    Hi.
    I saved this one for a rainy day
    Workaround without resorting to API's.
    The Listbox height is adjusted to 12 items high so that the listbox scroll bar does not automatically show.
    A vertical scroll bar is placed adjacent to the listbox.
    By reading the value of the scroll bar (this is set at the max of the list length).
    With a little bit of maths magic the list is constantly loading 'on the fly' as the scrollbar is moved.
    Keeping record of the actual list/sheet row, the three labels show the listbox selection.
    torachan.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-04-2014
    Location
    Portugal
    MS-Off Ver
    XL2013/XL365
    Posts
    83

    Re: Highlight visible items of ListBox based on a condition

    Hi torachan

    Thanks for your solution

    Look similar solution from KjBox.

    I am trying a solution with two ListBox, synchronized.

    But still waiting for an example with API.

    Many thanks
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    3,258

    Re: Highlight visible items of ListBox based on a condition

    Good morning.
    Just had a run through with the solution in post #5.
    The highlighters do not stay in sync with the listbox, why are there 13 lines in list and only 12 highlighters and the last row does not load.
    Also I was unable to select items from the listbox, not immediately obvious why not.
    Attached new version with the code compacted.
    Also looked at API solution, does not seem possible with VBA although appears possible in VB 6.0 ????
    torachan.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    11-04-2014
    Location
    Portugal
    MS-Off Ver
    XL2013/XL365
    Posts
    83

    Re: Highlight visible items of ListBox based on a condition

    Hi torachan

    Thanks for the new version, I will have to study the code to try to get to the real case.

    As for the number of items that appear, it must have resulted from some misconfiguration in the size of the ListBox, made by the code author, as well as not being able to select an item directly in the ListBox, all situations that have already been resolved.

    As for the API solution, see the site they sent me, I don't understand anything, maybe you can - http://www.cpearson.com/excel/DetectScroll.htm

    Thanks
    Jorge Cabral

+ 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. Highlight (visible) rows Based on cell value
    By ptmuldoon in forum Excel General
    Replies: 1
    Last Post: 07-27-2018, 04:45 PM
  2. [SOLVED] Listbox Selected Items (worksheets) visible
    By sunjam in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-16-2017, 01:50 AM
  3. [SOLVED] Filter and Copy Visible cells based on condition
    By kittu55 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-06-2017, 09:31 PM
  4. Replies: 1
    Last Post: 01-23-2015, 12:36 PM
  5. Making a command button visible based on Listbox selection
    By jlwiii34 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-13-2013, 01:56 PM
  6. 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
  7. Multi select Listbox Items selection based on other Listbox item selection.
    By srinivassathi in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-20-2011, 05:53 AM

Tags for this Thread

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