Highlight visible items of ListBox based on a condition

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • 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

  • 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

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • 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

  • In UserForm1 I've limited the data to 12 rows for your labels and renamed each Label:- lbl0, lbl1, lbl2 etc.


    I've added a second userform example using two ListBoxes. When this loads the first listbox shows X if the third column is empty and a tick mark if not.




  • 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

  • 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

  • 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



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


    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


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


    see Chip's explanation : http://www.cpearson.com/EXCEL/DetectScroll.htm

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • 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



    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.




    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

  • Hello again,


    Let me quickly recap the situation for you :


    1. You are focused on a preconceived idea for the solution ... instead of exposing your objective ...


    2. VBA seems to be relatively new to you


    My recommendation to visually differentiate your items :


    1. Forget your solution and subclassing ...


    2. Insert a Column A ... with a formula such as =IF(D1<>"",CHAR(149),CHAR(150))


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

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


    At least with my second example it reflects the matching items.

  • 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

  • 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

  • As Carim says, I think the easist way would be to add your checkmark by a formula. It requires a little setting up but will be effective.


    Use AutoCorrect to add a Checkmark


    First add a column to the left


    Copy a checkmark from some where like:


    https://coolsymbol.com/check-mark-symbols-tick-symbols.html


    In Excel:


    Now when you type chk in a cell it will display a checkmark. Instead of typing use the formula in A1 =IF(D1>"","chk","")


    Change the ListBox to show 4 Columns

  • 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

  • Maybe like this.


    The List Box and the Labels are contained within a Frame, the frame width is reduced so that the scrollbar for the list box is not visible. A separate ScrollBar control is placed next to the frame. Changes to that scrollbar changes the .ListIndex of the list box and adjusts the label colours accordingly.

  • 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

  • I didn't realise you wanted to be able to select an item in the list box. All you need to do is set the ListBox1 Locked property to False rather than True

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • I really cannot think of another way to achieve what you are doing.


    When I make UserForms I try to make them fairly dynamic. You could start by using a Table for the data, which would always reflect addition or removals of rows.


    Can you attach the real workbook, with sensitive data changed.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!