Highlight visible items of ListBox based on a condition

**JCabral** · 10-17-2019, 07:50 AM

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

**Leith Ross** · 10-17-2019, 02:03 PM

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.

**JCabral** · 10-17-2019, 03:58 PM

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

**torachan** · 10-21-2019, 12:27 PM

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.

**JCabral** · 10-21-2019, 01:27 PM

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

**torachan** · 10-22-2019, 04:01 AM

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.

**JCabral** · 10-22-2019, 07:54 AM

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

Highlight visible items of ListBox based on a condition

LinkBack

Thread Tools

Rate This Thread

Display

Highlight visible items of ListBox based on a condition

Re: Highlight visible items of ListBox based on a condition

Re: Highlight visible items of ListBox based on a condition

Re: Highlight visible items of ListBox based on a condition

Re: Highlight visible items of ListBox based on a condition

Re: Highlight visible items of ListBox based on a condition

Re: Highlight visible items of ListBox based on a condition

Thread Information

Users Browsing this Thread

Similar Threads

Highlight (visible) rows Based on cell value

[SOLVED] Listbox Selected Items (worksheets) visible

[SOLVED] Filter and Copy Visible cells based on condition

Can you find the quartile of an array based on a condition and using only visible cells??

Making a command button visible based on Listbox selection

Transfer random listbox items to new listbox and then loop through selected items

Multi select Listbox Items selection based on other Listbox item selection.

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions