RE: Identifying negative numbers in a Rowsource Listbox...

[chazrab]

RE: Identifying negative numbers in a Rowsource Listbox...

The Rowsource of Listbox1 on Userform1 is A1:A5 and has these values

A1 =1
A2 =2
A3 =-3
A4 =-1
A5 =1

Is there a way to identify and display the negative values in A3 =-3 and A4 =-1
in this Rowsource in Textboox1 and Textbox2 on Userform1 and have them display as
(3) and (1) in red fonts using something like Textbox1.Font.Color = red ?

I'm not experienced enough on using an array as a Listbox Rowsource and using code to identify negative elements of the array if that is one way to do it if nothing simpler can be done

Would appreciate anyone's help with this very much.

Thanks, cr

 

[mikerickson]

Re: Identifying negative numbers in a Rowsource Listbox...

All of the entries in a ListBox have to be formatted the same, so the (3) idea won't work.

If it is a MultiSelect List box you could run this code which will highlight the negative values

Dim i As Long

With ListBox1
    For i = 0 To .ListCount - 1
        .Selected(i) = (Val(.List(i)) < 0)
    Next I
End With

I would advise agains using RowSource. You have much more flexibility if you use the List property to set the values of the listbox.

Private Sub Userform_Intialize

    ListBox1.List = Range("A1:A5").Value

End Sub

 

[chazrab]

Re: Identifying negative numbers in a Rowsource Listbox...

All of the entries in a ListBox have to be formatted the same, so the (3) idea won't work.

If it is a MultiSelect List box you could run this code which will highlight the negative values

Dim i As Long

With ListBox1
    For i = 0 To .ListCount - 1
        .Selected(i) = (Val(.List(i)) < 0)
    Next I
End With

I would advise agains using RowSource. You have much more flexibility if you use the List property to set the values of the listbox.

Private Sub Userform_Intialize

    ListBox1.List = Range("A1:A5").Value

End Sub

I started playing around with the idea of coding an autofilter process after I posted this. I
came up with this and it seems to work:

Private Sub cmdfilter_Click()
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Sheets("REALBALS").Select
    Range("A1:B1").Select
    Selection.AutoFilter
    With Sheets("REALBALS").Range("B1").CurrentRegion.Offset(1, 0)
    .AutoFilter field:=2, Criteria1:="<0"
    .SpecialCells(xlVisible).Copy Sheets("REPORT").Range("A1")
    .AutoFilter
    End With
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

REALBALS is the sheet that has the data to use autofilter in 54 rows in col A, a date range and col B, the corresponding currency values on for that date on each row. The result gives the date each negative balance appears to its right. You have helped me in the past - thanks again very much.

cr
Kingwood Tx