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

chazrab

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

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,906
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
584
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,499
Messages
5,596,517
Members
414,074
Latest member
Matthew Kakde

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