filtering lists

[johny]

Hello Here's what Im hoping for
I have a list of Names in col A
.A... Mon
John 7:00 15:00
Jim 11:30 8:00
Nancy
Bob
Beside the name in Col B and C I have Mondays
Availability
On a seperate sheet I will have d6 and e6
as empty boxes to put in (lets say)
5:00 then in d6 8:00
below it I want a drop down to see the times entered and to ONLY show the
names Available for 5:00 to 8:00 which is JIm
How can I filter the availibilty threw a macro or something else in mind Anything would be a help Thanks John

 

[Damon Ostrander]

Hi Johny,

The following solution involves naming the range containing the table of values NameTable. To do this select the entire list containing the names and times and type "NameTable" in the Name Box in the formula bar above cell A1.

This macro must be placed in the event code module of the worksheet containing the ComboBox and the two cells containing the start and end time. To do this, right-click on this worksheet's tab, select View Code, and paste the following code into the VBE code pane. The code will immediately be in effect so that the dropdown will be populated with the names that meet the time criteria.

Private Sub ComboBox1_DropButtonClick()
Dim iRow As Integer
ComboBox1.Clear
For iRow = 1 To [NameTable].Rows.Count
If [NameTable].Cells(iRow, 2) >= [d6] _
And [NameTable].Cells(iRow, 3) <= [e6] Then
'add to dropdown
ComboBox1.AddItem [NameTable].Cells(iRow, 1)
End If
Next iRow
End Sub

In the example you gave I assumed that the a.m. and p.m. time designators were missing, since otherwise the problem doesn't make sense to me.

 

[eliW]

On 2002-08-31 13:27, johny wrote:
Hello Here's what Im hoping for
I have a list of Names in col A
.A... Mon
John 7:00 15:00
Jim 11:30 8:00
Nancy
Bob
Beside the name in Col B and C I have Mondays
Availability
On a seperate sheet I will have d6 and e6
as empty boxes to put in (lets say)
5:00 then in d6 8:00
below it I want a drop down to see the times entered and to ONLY show the
names Available for 5:00 to 8:00 which is JIm
How can I filter the availibilty threw a macro or something else in mind Anything would be a help Thanks John

Hi Johny,

Mondays are from 00:00 - 24:00
What is then: Jim 11:30 8:00?
and also how 5:00 to 8:00 are included in 11:30 8:00?

Eli

 

[johny]

DAMON Thanks for the macro
Thats much better then what I had
Quick question if you know
I have a combo box in col A when I doubleclick
one popup for the selected cell. but
i have a macro to run which selectes other
cells and when I choose the list name it
pasted in the active cell.How can I run
macro and not really select other cells.
THanks JOhn

 

[Damon Ostrander]

Hi again John,

It is almost never necessary to select cells in order to modify them in VBA. In addition to being unnecessary, it creates problems like the one you are experiencing as well as forcing the sheet to update, which slows down execution (this can be a big deal on a long macro). If you would like to post or email me your code I would be happy to show you how to write it so that no cells are selected/activated.

Damon