| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Remove Item from Listbox by Item Name | Excel Programming | |||
| Remove items from a listbox | Excel Programming | |||
| remove item fr listbox (correction) | Excel Programming | |||
| listbox remove Item | Excel Programming | |||
| Remove all Listbox items | Excel Programming | |||
Linear Mode
