remove items from listbox together with add item
 

Hi ,
I try to create a user form which contains 1 listbox and i wrote a
code to add items to a list box which only entered today. But I want
also consider hours for those entry.So lets say if for today user can
also able to choose time frame(like from14:00 to 16:00 etc).I am only
thinking to add the items for that date then remove those items which
are not in that time frame. But I really stuck. Can any one give me a
hand or give me suggestions. my code for now as follows
By the way that code only for commandbutton, All the names set.
Private Sub UserForm_Initialize()
Set reqd = ThisWorkbook.Names("req").RefersToRange
End Sub
sub GetTheList()
Dim Cell As Range
Dim trddate As Variant
trddate = Date
For Each Cell In reqd.Columns(1).Cells
If Cell.Value = trddate Then
UserForm1.ListBox1.AddItem Cell.Offset(0, 1).Value
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCou nt - 1,
2) = Cell.Offset(0, 2)
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCou nt - 1,
3) = Cell.Offset(0, 3)
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCou nt - 1,
4) = Cell.Offset(0, 4)
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCou nt - 1,
5) = Cell.Offset(0, 5)
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCou nt - 1,
6) = Cell.Offset(0, 6)
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCou nt - 1,
7) = Cell.Offset(0, 7)
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCou nt - 1,
8) = Cell.Offset(0, 8)
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCou nt - 1,
9) = Format(Cell.Offset(0, 9), "hh:mm")
UserForm1.ListBox1.ColumnHeads = True
UserForm1.ListBox1.TextColumn = 9
End If
Next
end sub

remove items from listbox together with add item
 

Instead of adding and deleting, could you just clear the listbox and add what
you want.

It might make it simpler.

" wrote:

Hi ,
I try to create a user form which contains 1 listbox and i wrote a
code to add items to a list box which only entered today. But I want
also consider hours for those entry.So lets say if for today user can
also able to choose time frame(like from14:00 to 16:00 etc).I am only
thinking to add the items for that date then remove those items which
are not in that time frame. But I really stuck. Can any one give me a
hand or give me suggestions. my code for now as follows
By the way that code only for commandbutton, All the names set.
Private Sub UserForm_Initialize()
Set reqd = ThisWorkbook.Names("req").RefersToRange
End Sub
sub GetTheList()
Dim Cell As Range
Dim trddate As Variant
trddate = Date
For Each Cell In reqd.Columns(1).Cells
If Cell.Value = trddate Then
UserForm1.ListBox1.AddItem Cell.Offset(0, 1).Value
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCou nt - 1,
2) = Cell.Offset(0, 2)
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCou nt - 1,
3) = Cell.Offset(0, 3)
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCou nt - 1,
4) = Cell.Offset(0, 4)
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCou nt - 1,
5) = Cell.Offset(0, 5)
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCou nt - 1,
6) = Cell.Offset(0, 6)
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCou nt - 1,
7) = Cell.Offset(0, 7)
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCou nt - 1,
8) = Cell.Offset(0, 8)
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCou nt - 1,
9) = Format(Cell.Offset(0, 9), "hh:mm")
UserForm1.ListBox1.ColumnHeads = True
UserForm1.ListBox1.TextColumn = 9
End If
Next
end sub

--

Dave Peterson

remove items from listbox together with add item
 

On Sep 22, 9:00 pm, Dave Peterson wrote:
Instead of adding and deleting, could you just clear the listbox and add what
you want.

It might make it simpler.



" wrote:

Hi ,
I try to create a user form which contains 1 listbox and i wrote a
code to add items to a list box which only entered today. But I want
also consider hours for those entry.So lets say if for today user can
also able to choose time frame(like from14:00 to 16:00 etc).I am only
thinking to add the items for that date then remove those items which
are not in that time frame. But I really stuck. Can any one give me a
hand or give me suggestions. my code for now as follows
By the way that code only for commandbutton, All the names set.
Private Sub UserForm_Initialize()
Set reqd = ThisWorkbook.Names("req").RefersToRange
End Sub
sub GetTheList()
Dim Cell As Range
Dim trddate As Variant
trddate = Date
For Each Cell In reqd.Columns(1).Cells
If Cell.Value = trddate Then
UserForm1.ListBox1.AddItem Cell.Offset(0, 1).Value
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCou nt - 1,
2) = Cell.Offset(0, 2)
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCou nt - 1,
3) = Cell.Offset(0, 3)
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCou nt - 1,
4) = Cell.Offset(0, 4)
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCou nt - 1,
5) = Cell.Offset(0, 5)
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCou nt - 1,
6) = Cell.Offset(0, 6)
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCou nt - 1,
7) = Cell.Offset(0, 7)
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCou nt - 1,
8) = Cell.Offset(0, 8)
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCou nt - 1,
9) = Format(Cell.Offset(0, 9), "hh:mm")
UserForm1.ListBox1.ColumnHeads = True
UserForm1.ListBox1.TextColumn = 9
End If
Next
end sub

--

Dave Peterson

hi,
Thanks but this workbook is gonna share by 100 other user from time to
time so i cannot simply set the time frame and add the items. Cause
some of the users enters the values earlier and some not. There is
only one control computer which need to see those entries according
to time frame so that I need to remove by using combobox1 and combo2
which will give me to choose time frame.
Thanks again for your reply

remove items from listbox together with add item
 

I don't understand.

It looks like you initially populate the listbox based on some date. So why
can't you clear that listbox based on a change to a combobox and loop again
through the range, but this time, look at the date and time?

" wrote:

On Sep 22, 9:00 pm, Dave Peterson wrote:
Instead of adding and deleting, could you just clear the listbox and add what
you want.

It might make it simpler.



" wrote:

Hi ,
I try to create a user form which contains 1 listbox and i wrote a
code to add items to a list box which only entered today. But I want
also consider hours for those entry.So lets say if for today user can
also able to choose time frame(like from14:00 to 16:00 etc).I am only
thinking to add the items for that date then remove those items which
are not in that time frame. But I really stuck. Can any one give me a
hand or give me suggestions. my code for now as follows
By the way that code only for commandbutton, All the names set.
Private Sub UserForm_Initialize()
Set reqd = ThisWorkbook.Names("req").RefersToRange
End Sub
sub GetTheList()
Dim Cell As Range
Dim trddate As Variant
trddate = Date
For Each Cell In reqd.Columns(1).Cells
If Cell.Value = trddate Then
UserForm1.ListBox1.AddItem Cell.Offset(0, 1).Value
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCou nt - 1,
2) = Cell.Offset(0, 2)
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCou nt - 1,
3) = Cell.Offset(0, 3)
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCou nt - 1,
4) = Cell.Offset(0, 4)
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCou nt - 1,
5) = Cell.Offset(0, 5)
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCou nt - 1,
6) = Cell.Offset(0, 6)
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCou nt - 1,
7) = Cell.Offset(0, 7)
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCou nt - 1,
8) = Cell.Offset(0, 8)
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCou nt - 1,
9) = Format(Cell.Offset(0, 9), "hh:mm")
UserForm1.ListBox1.ColumnHeads = True
UserForm1.ListBox1.TextColumn = 9
End If
Next
end sub

--

Dave Peterson

hi,
Thanks but this workbook is gonna share by 100 other user from time to
time so i cannot simply set the time frame and add the items. Cause
some of the users enters the values earlier and some not. There is
only one control computer which need to see those entries according
to time frame so that I need to remove by using combobox1 and combo2
which will give me to choose time frame.
Thanks again for your reply

--

Dave Peterson

remove items from listbox together with add item
 

Dave Peterson
Hi,
I agree i tried what you said it worked well.Thanks.But I still have
one more problem; why am I getting if I make the code as
following?
bir = CPitUF.ComboBox1.Value
For Each Cell In reqd.Columns(1).Cells

If Cell.Value = trddate And Cell.Offset(0, 5) = bir Then Or
Cell.ofset(0, 9) = bir) Then ' { HERE I GET THE ERROR}
' BUT W/O SECOND "OR" IT WORKS VERY WELL
CPitUF.ListBox1.AddItem Cell.Offset(0, 1).Value
CPitUF.ListBox1.List(CPitUF.ListBox1.ListCount - 1, 2) =
Cell.Offset(0, 2)
CPitUF.ListBox1.List(CPitUF.ListBox1.ListCount - 1, 3) =
Cell.Offset(0, 3)
end if
next

remove items from listbox together with add item
 

You have misspelled .offset(). You have Or following the Then.

If (Cell.Value = trddate _
And Cell.Offset(0, 5) = bir) _
Or Cell.offset(0, 9).value = bir Then ' { HERE I GET THE ERROR}

I'm not sure where the ()'s should go.

" wrote:

Dave Peterson
Hi,
I agree i tried what you said it worked well.Thanks.But I still have
one more problem; why am I getting if I make the code as
following?
bir = CPitUF.ComboBox1.Value
For Each Cell In reqd.Columns(1).Cells

If Cell.Value = trddate And Cell.Offset(0, 5) = bir Then Or
Cell.ofset(0, 9) = bir) Then ' { HERE I GET THE ERROR}
' BUT W/O SECOND "OR" IT WORKS VERY WELL
CPitUF.ListBox1.AddItem Cell.Offset(0, 1).Value
CPitUF.ListBox1.List(CPitUF.ListBox1.ListCount - 1, 2) =
Cell.Offset(0, 2)
CPitUF.ListBox1.List(CPitUF.ListBox1.ListCount - 1, 3) =
Cell.Offset(0, 3)
end if
next

--

Dave Peterson

remove items from listbox together with add item
 

Hi,
Thanks Dave, you are great,I really couldn`t see that.But I have one
more question to ask;as you can see my list does not have the same
array, is there any way to type column headsof the listbox?
thanks a lot for your help

remove items from listbox together with add item
 

You can use column headers only if you point at a range using rowsource.
There's a .columnheads property that you can change.

So if you're not going to use a contiguous range, the options a
#1. Move the matching data to a temporary worksheet (with headers) and use that
range as your .rowsource.
#2. Add some labels above the listbox and try to make look as nice as you can.

" wrote:

Hi,
Thanks Dave, you are great,I really couldn`t see that.But I have one
more question to ask;as you can see my list does not have the same
array, is there any way to type column headsof the listbox?
thanks a lot for your help

--

Dave Peterson