Create Excel userform listbox with filtered data

[MISMonkey]

I have searched many forms for a match to this question to no avail but if you know of one please point me in the right direction.

I have a sheet that records meeting room bookings. Each room has a unique ID (column B) and each booking has a date (Column h) and a start and end time of the booking (columns i & j). Users enter bookings using userforms and they can check for availability at give times prior to booking. Bookings are in 15 minute increments between 7am and 7pm.

what I want to do on a user form is be able to show the user the diary for the day for a particular room. In essence I want to filter my booking sheet for a particular ID on a particular date.

The kick is... I want the listbox to display the results so that it shows every 15 minute slot from 7am - 7pm for the selected day and room and alongside each slot it will be blank (if not booked out OR it will contain the name of the person who has it booked (column M of my booking sheet).

Logic suggests I could perform some embedded looping for every time slot go and check if a room ID and date match what the user wants and if it does build a string to add to the listbox. This feels very inneficiant and I'm wondering if there is a quicker / smarter way....?

I love deadlines. I like the whooshing sound they make as they fly by. (Douglas Adams)

 

[SkipVought]

hi,

Logic suggests I could perform some embedded looping

I would do a Query, via MS Qery, using Data > Get External Data > Excel Files -- YOUR WORKBOOK >> Select Your Sheet, assuming that one sheet contains your table and nothing else. Criteria as selected on the user form.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MISMonkey]

Thanks Skip.

I was assuming that I could maybe load the data on the tab into a recordset and then filter / manipulate the data that way. I'm battling through some articles to try and fathom how to do it. If anyone has an example of how to do this in Excel it would be appreciated.

I will take a look at the MSQuery route as well.

I love deadlines. I like the whooshing sound they make as they fly by. (Douglas Adams)

 

[SkipVought]

MS Query is easier, as there will be very little VBA code required, as opposed to ActiveX Data Objects, which is ALL VBA. It all depends on your requirements. I use both methods often.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]