Change the backcolor of listbox using VBA.

[atf32]

Need code for changing the backcolor of a listbox (on the surface of excel), through VBA (excel 2003). The color is "&H0050D092&" ....."yes" need to use this color.

 

[Leith Ross]

Hello atf32,

There are 2 types of ListBox controls: Forms and ActiveX (Control Tollbox). With the Forms control you can not change the back color. With the ActiveX control you can by setting the BackColor property.

ActiveX Control on a Worksheet Example

    ActiveSheet.OLEObjects("ListBox1").Object.BackColor = &H50D092

EDIT: You can replace "ActiveSheet" with the worksheet that ListBox is on like, "Worksheets("Sheet1").OLEObjects("ListBox1").Object.BackColor = &H50D092

 

[atf32]

This helped....Have another related question.......

I have two lisbox. If user select a certain value in listbox1, then listbox2 gets turned to a certain color. I have this down. But when the backcolor for listbox2 is changed, the selected value in listbox2 is lost. Is it possible to keep it?

 

[Leith Ross]

Hello atf32,

After changing the color of the second List Box, add DoEvents immediately after it. This will cause the List Box to be repainted on the Worksheet.

 

[mikerickson]

With ListBox2
    IndexMemory = .ListIndex
    .BackColor = newColor
    .ListIndex = IndexMemory
End with