How can you change the background colour of a listbox when it's populated?

[papic1972]

Hi All,

Does anybody know how to change the background colour of a listbox when it is populated by data?

 

[papic1972]

I've come up with this sub routine that i call from a command button which populates the list box:

Sub VbColour1()
'---------------------------------------------------------
On Error GoTo Err

If Forms!frmAtaGlance!lstDriverGlance1.RowSource = Null Then
Forms!frmAtaGlance!lstDriverGlance1.BackColor = vbWhite
Else
Forms!frmAtaGlance!lstDriverGlance1.BackColor = vbRed
End If

Err_VbColour1:
Exit Sub
Err:
MsgBox "Error: " & Err.Number & " " & Err.Description, vbCritical + vbOKOnly, "VbColour1"
Resume Err_VbColour1
Resume

End Sub


It's not working 100%, i don't think i've coded this line correctly "If Forms!frmAtaGlance!lstDriverGlance1.RowSource = Null Then"

can anyone offer a suggestion?

 

[John Big Booty]

You can set the Back Colour of the list box whilst the form is in design view, or you can set via one of the form events with something like;

    If Me.ListBoxName.Recordset.RecordCount = 0 Then
        Me.ListBoxName.BackColor = 16711935  [COLOR="SeaGreen"]'Set BackColor if no data[/COLOR]
    Else
        Me.ListBoxName.BackColor = 16776960  [COLOR="SeaGreen"]'Set BackColor if data[/COLOR]
    End If

 

[John Big Booty]

Ah whilst I was typing

Use IsNull(Me.ControlName) to test for control with Null values.

You possibly want to test Forms!frmAtaGlance!lstDriverGlance1.Recordset.RecordCount = 0 rather than Null

 

[papic1972]

This is really weird.
I tried:
Forms!frmAtaGlance!lstDriverGlance1.Recordset.RecordCount = 0 and i get the following error:
Error 3420: Object Invalid or no longer set

If i use IsNull(Forms!frmAtaGlance!lstDriverGlance1) then it colours the listbox even if it doesn't have any data in it.

Do you have any ideas?

 

[boblarson]

You guys are adding .Recordset.RecordCount into a listbox and that doesn't exist.

You should be looking for

If Forms!frmAtaGlance.lstDriverGlance1.Items.Count > 0 Then

 

[papic1972]

Hi Bob,

I just tried your suggestion and i get the following error:

Error 438: Object doesn't support this property of method.

Would i be getting this because i am using a pass through query to feed the listbox?

 

[boblarson]

Hi Bob,

I just tried your suggestion and i get the following error:

Error 438: Object doesn't support this property of method.

Would i be getting this because i am using a pass through query to feed the listbox?

Sorry, my bad - it is LISTCOUNT not Items.Count

So

If Forms!frmAtaGlance.lstDriverGlance1.ListCount > 0 Then

 

[papic1972]

Yes Bob, that is it!!!

My sub-routine now works perfectly!!
It looks like this now:

Sub VbColour1()
'---------------------------------------------------------
On Error GoTo Err

If Forms!frmAtaGlance!lstDriverGlance1.ListCount > 0 Then
Forms!frmAtaGlance!lstDriverGlance1.BackColor = 16711935 '(If there is data, then use this colour)
Else

Forms!frmAtaGlance!lstDriverGlance1.BackColor = 16777215 '(If there is no data, then use this colour)
End If

Err_VbColour1:
Exit Sub
Err:
MsgBox "Error: " & Err.Number & " " & Err.Description, vbCritical + vbOKOnly, "VbColour1"
Resume Err_VbColour1
Resume

End Sub


Thank you heaps to both Bob & John!!

 

[John Big Booty]

You guys are adding .Recordset.RecordCount into a listbox and that doesn't exist.

You should be looking for

If Forms!frmAtaGlance.lstDriverGlance1.Items.Count > 0 Then

Doh!