Re: Listbox by Dave
Dave
Fri May 09 17:12:37 CDT 2008
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