Listbox

[Ronbo]

I have four columns A. 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]

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]

Thanks a lot for the idea and code. It works perfect with the exception that
it does not populate the combobox upon opening. I have to click on the right
slider bar and then it shows the list below. It would be nice to have the
list displayed in the combobox upon opening. Is there a way to do that?

Ronbo

 

[Dave Peterson]

How would you know what category to use?

And I'm not sure what right slider bar you mean--the combobox?

But you could add a line to the _initialize event that would populate the
combobox:

....

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

Me.ComboBox1.ListIndex = 0 '<--- added

End Sub


..listindex = 0 is the top choice, 1 is the second, ...