I would add a new sheet (hidden???) that would hold the categories that I
liked.

Then I would use a combobox that allowed the user to choose from those
categories. When the combobox is changed, the entries in the listbox would
change.

I built a small userform with a combobox, a listbox and two commandbuttons.
This is the code behind the userform:

Option Explicit
Private Sub ComboBox1_Change()

Dim myCell As Range
Dim myRng As Range

With Worksheets("SortSheet")
Set myRng = .Range("d1", .Cells(.Rows.Count, "d").End(xlUp))
End With

Me.CommandButton2.Enabled = False

With Me.ListBox1
.Clear
If Me.ComboBox1.ListIndex < 0 Then
.Enabled = False
Else
.Enabled = True
For Each myCell In myRng.Cells
If InStr(1, myCell.Value, _
Me.ComboBox1.Value, vbTextCompare) 0 Then
.AddItem myCell.Value
.List(.ListCount - 1, 1) = myCell.Offset(0, -3).Value
.List(.ListCount - 1, 2) = myCell.Offset(0, -2).Value
.List(.ListCount - 1, 3) _
= Format(myCell.Offset(0, -1).Value, "mmmm dd, yyyy")
End If
Next myCell
End If
End With

End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim iCtr As Long

With Me.ListBox1
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
MsgBox .List(iCtr, 0) & vbLf _
& .List(iCtr, 1) & vbLf _
& .List(iCtr, 2) & vbLf _
& .List(iCtr, 3)
End If
Next iCtr
End With

End Sub
Private Sub ListBox1_Change()
Dim iCtr As Long

Me.CommandButton2.Enabled = False
With Me.ListBox1
For iCtr = 0 To Me.ListBox1.ListCount - 1
If .Selected(iCtr) Then
Me.CommandButton2.Enabled = True
Exit For
End If
Next iCtr
End With
End Sub
Private Sub UserForm_Initialize()

With Worksheets("Categories")
Me.ComboBox1.List _
= .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)).Value
End With

With Me.CommandButton1
.Cancel = True
.Caption = "Cancel"
End With

With Me.CommandButton2
.Default = True
.Caption = "Ok"
.Enabled = False
End With

With Me.ListBox1
.ColumnCount = 4
.Enabled = False
.MultiSelect = fmMultiSelectMulti '???
End With

End Sub

A couple of notes:

I look for the category anywhere in the category cell with this line:

If InStr(1, myCell.Value, _
Me.ComboBox1.Value, vbTextCompare) 0 Then

I formatted the dates the way I like <bg with this line:

.List(.ListCount - 1, 3) _
= Format(myCell.Offset(0, -1).Value, "mmmm dd, yyyy")

And I allow the user to select more than one option in the listbox with this
line:

.MultiSelect = fmMultiSelectMulti '???




Ronbo wrote:

I have four columns A:D. A=date, b=payee, c=catagory and d=amt. I am trying
to create a listbox based upon the catagory. In other words I want to know
all payments that have been for "entertainment". I have been trying some
code I found from Dave P. as follows;

Option Explicit
Private Sub UserForm_Initialize()

Dim myCell As Range
Dim myRng As Range
Dim myWord As String

myWord = " Entertainment"

With Worksheets("SortSheet")
Set myRng = .Range("d1", .Cells(.Rows.Count, "d").End(xlUp))
End With

For Each myCell In myRng.Cells
If LCase(Left(myCell.Value, Len(myWord))) = LCase(myWord) Then
Me.ListBox1.AddItem myCell.Value
End If
Next myCell
End Sub

It returns "Entertianment" from the category column for each entry. I also
need for it to include the date, payee and amount.

The second question is how can I make the catagory a variable that is
selected by clicking on a cell with the the category name... such as gas,
supplies, etc so that it will show a list of all entries (without a routine
for each).

Thanks for any help
Ronbo

--

Dave Peterson