Add background colour to ListBox item(s) that have been selected...

[Matty]

Hi Team,

I have a UserForm ListBox that is populated via a SQL connection. When a value is selected, a delimited string containing the selected values is created in a cell on a spare worksheet.

I would like the background colour of the ListBox item to be a different colour if it exists within the string held on the spare sheet mentioned above.

Can this be done?

Thanks,

Matty

 

[RoryA]

It can be done, but it's complicated and requires several API calls. This example from Andy Pope will give you some idea of how it could be done: http://www.andypope.info/vba/colourdropdown.htm

 

[Matty]

Hi Rory,

That does look more complicated than I was expecting/hoping!

Changing the background colour was just one option I was looking at. Is there an easier way/different method I could employ for identifying the ListBox items that exist on another sheet (i.e. the ones that have been selected already)?

Cheers,

Matty

 

[RoryA]

Depending on what you are doing, you could just pre-select them in the list?

 

[Matty]

Hi again,

Here's a brief summary of the set up:

• The UserForm contains two ListBoxes: the first ListBox contains hierarchy descriptions and the second ListBox contains the values that exist within the hierarchy selected.

• The second ListBox is dynamically populated with data from SQL upon the user right clicking a hierarchy description from the first ListBox. They then have the ability to chose the values they wish from it.

• Because the second ListBox is dynamically populated, once a user moves to choose values from another hierarchy description, it's not simple to see where previously applied filters exist.

• To help identify them, I was wanting to highlight those hierarchy descriptions where underlying filters have already been applied.

The code itself works well (I think the right click functionality was one of Andy Pope's!); it's just the issue of highlighting that I want in order to finish things off.

Hope you can help.

Cheers,

Matty

 

[RoryA]

I think that would get pretty ugly with a listbox. A ListView could work, assuming all users would have it installed?

 

[Matty]

The problem is that users are already familiar with the existing set up, so changing it is not really an option.

If colouring is a challenge, perhaps an additional character - say a "*" next to the hierarchy description - could work. Anything that indicates a filter has already been applied is all that's needed.

Cheers,

Matty

 

[RoryA]

You could do that, using whichever event you are currently using to populate the cells, or add the selections to a separate listbox.

 

[Matty]

Hi,

Went down the route of a separate ListBox, which is working well.

Thanks for the suggestion!

Matty

 

[RoryA]

Glad to help.