Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox
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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox
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, ... Ronbo wrote: 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" wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
userform listbox cannot get listbox.value to transfer back to main sub | Excel Programming | |||
avoiding duplicates in listbox (added from another listbox) | Excel Programming | |||
VBA: Creating listbox similar to the one in Pivot table (Listbox+Checkbox) | Excel Programming | |||
listbox.value not equal to listbox.list(listbox.listindex,0) | Excel Programming | |||
Is refreshing listbox rowsource in listbox click event possible? | Excel Programming |