Results 1 to 7 of 7
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    color-ize listbox (Excel 2003)

    I have 5 list boxes (from the Control toolbox) on a sheet that I use for multi-column items. Is there some Win API trick to highlight or color some of the items? The list is made up of part numbers, descriptions, prices and the source for this data is categorized with headings that I'd like to make more distinctive to the user. Right now I'm just using text like ">> sub-heading <<"" where this row in the list would have no data for the other columns. Example:
    <pre>Product Description Price
    ProdAA desc-AA $100
    ProdAB desc-AB $150
    >> Chassis <<
    ChassisCC desc-CC $250</pre>


    So I'd like some way to make it easier for the user to recognize that ">> Chassis <<" is a heading that applies to the next 'n' items. IThese headings (and I have sub-headings too) aren't items to be selected (although you can click them of course, but nothing happens). Any creative ideas?

    Thnx, Deb

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: color-ize listbox (Excel 2003)

    You may be able to adapt some of the examples from vbVision (originally for VB6) for your purpose.

    Have you thought about using two list boxes - one to display categories, and one to display items within the selected category? Or perhaps a TreeView control?

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: color-ize listbox (Excel 2003)

    WOW!! Thanks for the resource, I'll definitely see what it can do. The current text-based implementation is not at all intuitive. I haven't used tree-views before but I have 100's of these product names and I want to minimize the space I use on the sheet so I'll experiment with each solution.

    Thnx, Deb <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: color-ize listbox (Excel 2003)

    This ListBox UserControl might do what you're after.

    Alan

  5. #5
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: color-ize listbox (Excel 2003)

    Yes this one is pretty nice but I can't have a solution where I have to install a .dll on someone's PC, it has to be whatever Windows or Excel comes with on its own (no add-ins or other non-standard controls). I'll look at the other options that were posted to make sure they don't also require these added external objects. I didn't think any solution would be easy to implement but I wanted to make sure I wasn't over looking something obvious.

    One reply suggested having multiple boxes for each category but there are ~10 categories and that would be very crowded and unwieldy. I think the TreeView needs a hook to an external control too which I can't guarantee that my users have either. I'll check further though....

    One thing I could do is have a set of radio buttons for each sub-category and when you click on that category, it auto-populates the list to only contain the items under that category. I'll have to test that and see how it looks on the sheet and how important it is to the person I'm creating this GUI for.

    Thnx, Deb

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: color-ize listbox (Excel 2003)

    Unless I've misunderstood what's written on the site, I think the solution I posted is specifically aimed at NOT requiring any 3rd party "extras".
    For instance:

    " ... with the result that all of your UCs can subclass themselves internally. In other words, during intialization the UC calls a function(s) to set up one or more subclass routines, and the function then redirects the address of the routine to a WindowProc sub back inside the UC. Once set up, this method allows each instance of a UC to receive and handle its own messages. You only need a small external module and a single, one-line redirect function for each type of object that you are subclassing. So there is no limit to how many UC instances you can use and how many subclass routines you can handle. Your owner-drawn, UserControl-mounted component window becomes as easy to use as an ActiveX control, once you've fleshed it out with functionality, but it functions with the efficiency of the Windows API and has your own custom set of properties and methods. For instance, you could use existing API functions to add a line scrolling method like the following to an owner-drawn RichTextBox:
    CustomRTB.Scroll NumberOfLines, Direction"

    This is what I get from running the demo in VB6 - no extra components or references required.

    Alan

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: color-ize listbox (Excel 2003)

    Sorry Deb, I was asleep at the wheel a bit there, not realizing that you were targetting XL VBA, rather than VB6. While the link I provided requires no "extras" as a VB6 implementation, I don't think it lends itself to easy adaptation in VBA. However, AJP Excel Information - Colour Dropdown control is all done in XL VBA and might be what you're after... finally!

    I have used your idea of "progressive" listboxes before, with some success. It's not hard to implement and probably is a better way to present information from a heirarchical structure. I've attached a simple demo, in which the user begins selection from the top category and progressively drills down on an individual item. Just run the form from within the VBE. HTH, sorry for the bum steer.

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •