change listbox line colors

[depcdivr]

Is there a way to use different line colors within a listbox.

I want to display a series of 27 different lines but I want some lines to be green, some yellow, some red, etc based on some criteria. Sort of like conditional formatting but for a listbox.


can this be done in a listbox or are we limited to only having one color in the listbox?

Thanks

 

[MickG]

There's no easy way with a listbox, but you could use a Spreadsheet control, you can colour that.

 

[depcdivr]

I am not sure what you are thinking. Are you talking about just using a specific spreadsheet to display the data.

I am using a listbox because I am trying to display the data in a userform as opposed to a spreadsheet?

 

[MickG]

NB:- Not sure it its there on the latest Excell, but,
If you look through the additional controls for your userform , you should find one similar to :- "Microsoft Office Spreadsheet 10".
If you place that on your Userform , By using the "Commands & Options" properties (Right click control) you can remove all the Scrollbars Header, even the Grid, so it looks like a "Listbox".
You can then colour the Text/Cells either by Using the "Property Options or by VB.
Like:_

Spreadsheet1.Range("A1:D10") = ActiveSheet.Range("A1:d10").value
Spreadsheet1.Range("A12:d12").Interior.ColorIndex = 6

 

[depcdivr]

I was not aware of this option. I guess you learn something new every day.

Then I asusme that I populate the cells with something like

 spreadsheet1.cells(1,2)="hello"

When I first did this it comes up with an activeX warning. Then after that it does not. what was that warning about? How to I prevent it from coming up again?


Is there a way of removing the row/column labels(a,b,c etc)?

 

[MickG]

When you have the Userform in Design Mode, if you right click on the Userform spreadsheet control , the Sub Menu that appears should have "Commands & Options" you can Modify the control how you like.
NB:- I find that sometimes the "Commands & Options" Does not show on the Sub Menu and you have to right click in various spots on the control to get it to appear. this may be just my version.
If you right click the Spreadsheet control and select view code, then go to the drop down at the top right of the code window, you will see all the Code "Events" that you can use.
I don't know why you got an error with that code , it works ok for me.

 

[depcdivr]

Great Thanks.