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

chazrab

Well-known Member
Joined
Oct 21, 2006
Messages
700
RE: Identifying negative numbers in a Rowsource Listbox...

The Rowsource of Listbox1 on Userform1 is A1:A5 and has these values
Code:
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,208
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


Code:
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.


Code:
Private Sub Userform_Intialize

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

End Sub
 

chazrab

Well-known Member
Joined
Oct 21, 2006
Messages
700
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


Code:
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.


Code:
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:
Code:
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,279
Messages
5,836,367
Members
430,424
Latest member
ALHK022

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top