Input form with criterion-based selection list

**TPFKAS** · 12-24-2009, 11:51 AM

Hi Guys,
Thanks to the great help I was able to build the attached application. It's purpose is to keep track of take-offs and landings for our gliding club.
I am happy with the way I can add flights (using the button "Add flight") in the list now and I am now turning to a nice way to register landings.
I have implemented a macro that just puts a timestamp in the active cell and that works OK. I can use it to register the landing time.
However, I'd like to make it more fancy. I would like to have a button called "Close flight". When pressed a form or a window should appear with all open flights (i.e. all rows in the spreadsheet that are not empty but have no landing time, that is cell in col. G is empty). The user should then be able to select one. Press on a button called "Now" or enter a time manually which then is put into the spreadsheet.

Maybe a complicated story, but the attached example should make things clear.

Anybody care to help how I should achieve this?

TIA

**Palmetto** · 12-24-2009, 12:01 PM

It would be best if you provided a bit of data in the WB and told us which column contains the "flight" ID. As it stands, it is not clear which items should populate the list.

Here is a hint for one way to go about: automate Advanced Filter using criteria, delete non-relevant columns from the filtered results and create named range as the source list for a combo box (or listbox) in a new form.

**TPFKAS** · 12-24-2009, 12:41 PM

Hi Palmetto,

OK, here's the sheet again with some entered data.
I entered these three flights using the "Add flight"-button.
I also closed flight number 3 by entering a landing time manually in G10, so flights 1 and 2 are still open.
What I would like now is have a button that says "Close Flight". When this button is pushed a form or window should appear that only displays flight 1 and 2. Then select one, press a button to enter the current time as landing time.

Your hints make sense to me, but I need to figure out how to do that...

BTW: Merry Xmas...

**Palmetto** · 12-24-2009, 12:52 PM

Which column should be used to create the item list by which to select an open flight: Type, PIC, Pax, other?

**TPFKAS** · 12-24-2009, 01:04 PM

Could be any of them, but better not use "Pax" (which means passenger and there are flights without passenger).
But most logical would be to use "Start" which is the start time.
If that holds a value the aircraft is definitely in the air...

**Palmetto** · 12-24-2009, 01:19 PM

But most logical would be to use "Start" which is the start time.

This is not a good choice because there could be (and are in your example) duplicate time values. It needs to be a unique value that can be searched.

If TYPE is always unique, then this will work. Is this OK?

**TPFKAS** · 12-24-2009, 01:35 PM

Since I tend to the option of using this field for the aircraft registration rather than the aircraft type, yes, type will be unique.

**Palmetto** · 12-24-2009, 03:42 PM

OK. Here is a start. Rather than use Advanced Filter, I've just looped through the cells and added then as a list item in the combo box if the Land time is blank.

I created another form, which you can call up, which uses this code.

Note: I did not create any code to write the time value to the worksheet. You will need to do this.

Please Login or Register  to view this content.

**TPFKAS** · 12-24-2009, 06:39 PM

Hi Palmetto,
I greatly appreciate your support. I only had very short time to look at your revisions and I need some more time to study it and understand how this works. Next few days I'll be too busy with the family to do that but I certainly will get back overe here.

Seeya.

**TPFKAS** · 12-27-2009, 10:11 AM

I had a look at it and it's a very nice piece of code; works without any problem. I also found how to write the correct time value to the appropriate position in the worksheet.

However, I have one remaining issue:
If I open the form with the Listbox a second time, flights that have been closed earlier are still in the list. I tried to solve this by clearing the list after closing it, but if I then reopen, the list is empty. Apparently the Initialize only works the very first time the list is generated.

How can I make sure that the selection list is updated properly each time I open the form?

**TPFKAS** · 12-27-2009, 05:05 PM

I already found out.

I moved the piece of code to the module that also shows the form.
I had to ammend it a little.

Full code looks lik this:

Please Login or Register  to view this content.

Landing is the form to enter landing data
Openflights is the listbox that should display the flights that have no landed yet.

Gonna mark this thread as SOLVED. Thx Palmetto, I added to your reputation

Input form with criterion-based selection list

LinkBack

Thread Tools

Rate This Thread

Display

Input form with criterion-based selection list

Re: Input form with criterium-based selection list

Re: Input form with criterium-based selection list

Re: Input form with criterium-based selection list

Re: Input form with criterium-based selection list

Re: Input form with criterium-based selection list

Re: Input form with criterion-based selection list

Re: Input form with criterion-based selection list

Re: Input form with criterion-based selection list

Re: Input form with criterion-based selection list

Re: Input form with criterion-based selection list

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions