Highlight visible items of ListBox based on a condition

Hello

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?

Thanks
Jorge Cabral

NOTE: Also in here : https://www.excelforum.com/exc…based-on-a-condition.html

Quote from Carim

Hello,

A couple of remarks ...

1. With 12 visible items in your ListBox ... why haven't you, ... at the very least .... aligned your 12 Labels in the proper sequence... ?

2. If I am not mistaken : CrossPosting :

https://www.excelforum.com/excel-pro...condition.html

Display More

Hi Carim
Sorry, but I didn't understand your remarks, or how they can help me, although I didn't mention it, all of this in VBA is too new for me.
And yes, it's not your mistake, isn't it possible?

Thank you very much
JCabral

Quote from royUK

You haven't only loaded 12 rows into the ListBox, the rest are simply not visible, therefore you would need a Label for each Row in the ListBox

Hi royUK
Thank you for your answer.

I know that my ListBox doesn't only have 12 lines / items, what I mentioned is that only 12 items are visible, whenever I scroll up or down.

So what I want is what you did for Userform1, but without limiting the number of items to 12, and whenever I scroll up or down the color of the labels updates according to the value of ListBox column three, Red if it doesn't exist, Green if it exists.

The second example doesn't help me much for what I want. I didn't mention it, but this example is part of a larger Userform, from which I just removed this part.

Thank you very much
JCabral

Quote from Carim

1. When adding Labels in front of each ListBox item ... you should be consistent and have Label1 in front of ListBox Item 1, Label2 in front of ListBox Item 2, etc

If you still do not see what I mean ... check each one of your Labels ... and you will find out ...

- Now I understand and already rectified

Quote from Carim

2. Regarding CrossPosting ... I am not a moderator ...

BUT to help you understand about CrossPosting ... please also read this:

http://www.excelguru.ca/content.php?184

- I apologize, already rectified, mentioning that this request for help, is also in another forum.

Quote from Carim

3. Back to your request ... what you are asking for in terms of Scrolling ...without clicking ... does require SubClassing ...to detect Scrolling

- And how is it possible to do, what do you refer to in my example? Is there any function that tells me what is the Index of the first visible item in the ListBox? I think if I know what is the index of the first visible item I can do what I want, the problem is that I don't know how to find out the value of the first index of the first visible value of ListBox
Can you help me on this?

Thank you very much
JCabral

Hi Carim
Thanks again for your reply.

As you say, and focusing on the objective, in a Userform, not in a spreadsheet, how can I show that the value of the third column of the ListBox has a value or not, and for that I want the color of the Label tell me this as I scroll up and down

As you say, I'm new to these things in VBA, which is why I can't stick to preconceived solutions, because I don't know what the solution is, it's the help I'm asking for here. I just mention in the example that I put together which are the variables that I can not change, and that relates to the size of the ListBox and consequently the number of visible items.

Thanks for help

Quote from royUK

How will you determine which label refers to which line on the ListBox? Somehow it would need to constantly update as the ListBox is scrolled. I don't believe there are any ListBox events to do this.

This is my doubt, what I wanted is that the first Label was related to the first visible item of ListBox, if you can do this then the rest I think is easy. According to Carim is possible, the problem is that I know little of VBA and much less of what is "subclassing".

Thanks for help

Hello again Carim / royUK

First I apologize for just now answering.

Many thanks to both of you, but unfortunately this solution does not solve my problem, as the implications with the rest are very large.
Unfortunately it was only now that the problem came to me and what I thought was to see if I could find a solution that didn't mess with the rest I had implemented.
As I said this is part of a larger Userform, and the data is part of a table, which grows and shrinks every day, and that already exists, and adding one more column will make a lot of changes in everything else.

I'll see if I can find another way to solve this problem, which from what I've seen won't be easy at all.
If you have another suggestion I am very grateful.

Thank you very much
Jorge Cabral

Hi KjBox
Thanks for your reply.

This is an ingenious way to try to solve the problem, which does not fully solve as it does not allow you to select the items directly from the ListBox.

But with this solution came to me an idea, which I do not know if it is possible, also resulting from a solution presented by royUK, is it possible to synchronize two ListBox? If it were possible in one put the data and another put if there are data or not, and only allowed to move the "main" ListBox.

Is this possible?

How is it possible to do?

Thanks
Jorge Cabral