filtering lists

johny

Board Regular
Joined
Jul 15, 2002
Messages
69
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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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

Well-known Member
Joined
Mar 2, 2002
Messages
1,934
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

Board Regular
Joined
Jul 15, 2002
Messages
69
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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
 

Forum statistics

Threads
1,144,263
Messages
5,723,320
Members
422,492
Latest member
RobF2112

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top