Create booking system in excel!

Hi, I'm pretty new here but I'm in need of help badly. My job deepends on this. I am a field service engineer (system administrator) but for some reason, my office has demanded that i create an excel sheet with tools for them to be able to monitor their cars. i.e , the drivers, who booked the car, for how long, etc.

I am not a genius with Excel/VBA but all I was able to make is the front-end ( kindly see attached).

All i need is this.

When I click the
Date: I would like the users to be insert date from a calendar
Booked By: click and an arrow to insert their names from a list of employees
Driver's name: insert name from list of drivers
Car Type: insert car type from a list of cars
Register number:insert from a list
Depart time: insert time from a clock ( if possible)
Arrival time: insert time from a clock ( if possible)
Status: select between available ( blue color) & not available. (red color)


Also, the excel sheet will be placed on a network drive where users can access it and save.

Help please, because I just don't know what to do. Any advice or modification as welcome.

Many thanks.

There's a topic about listboxes on my site. This might help you to get data from a list.

http://www.excelguide.eu

Can you attach what you have si far?

ok guys...this is what I have now. ( Kindly see attached).

I am able to create a list of (drivers, cars etc) on another sheet and its taking shape.

Question now is,
1) how do I insert date and time.
2) How do I hide the second sheet so that users
dont see it at all.
3) to make status change in color, e.g if available (blue)..if not available RED.

When I click the

Date: I would like the users to be insert date from a calendar
this will need VBA

Booked By: click and an arrow to insert their names from a list of employees
Driver's name: insert name from list of drivers
Car Type: insert car type from a list of cars
Register number:insert from a list
Status: select between available ( blue color) & not available. (red color)

The above can be done with Data Validation see :
http://www.excel-it.com/data_validation.htm

Depart time: insert time from a clock ( if possible)
Arrival time: insert time from a clock ( if possible)

The above can be done with VBA

Add your data validation then post back for further help.

many thanks roy...I have done the data validation. the file is attached in my previous post. (test.xls)

can you see it?

what is left is the

Date: I would like the users to be insert date from a calendar

this will need VBA

Depart time: insert time from a clock ( if possible)
Arrival time: insert time from a clock ( if possible)

The above can be done with VBA

Originally Posted by BigBas

The front end looks nice, but how functional is it?

You haven't addressed a major concern: how will the information be databased. Specifically, once someone enters their information, what is supposed to happen to it? Will they print out the Car Booking? If not, when someone else enters their information, it will clear out the first person's info.

Do you have any ideas on proceeding? Or do we have a degree of free reign?

Hi BigBas and many thanks...I followed advices of earlier posters (WintE & RoyUk) to perform the data validation, which I have done. (see attached)

but then actually if you have more ideas about how to proceed..they will be highily welcomed. There is a high degree of free reign...))))

I dont know what to do with the database, which in fact is supposed to be stored. i.e if you enter a date/time and there is already a booking for that day, it should be reflected...

To add the time on road, switch on design view & double click the last time control(DTPicker3), this will open the VB Editor. Make sure the control's name is in the left hand drop down and in the right hand dropdown find Change. Paste in this code.

Please Login or Register  to view this content.

To change the status of a booking use Conditional Formatting see

http://www.excel-it.com/excel_condit...formatting.htm

Originally Posted by aretha

3. How do I edit a booking?

To edit a booking with the form would require a similar one setting up, you would need to add a reference number for each booking. This should show in column A of the database. Then use VLOOKUP to populate the editable cells. The code should then be adapted to overwrite the correct line.

5. How do I add arrows like the ones you inserted in the date row. Its looks and works easier than the ones in the booked by, drivers'name ,etc... row.

These are activex controls, the others are Data Validation. You would need to use ComboBoxes but it would need more coding.

I've just amended the earlier code.

It works fine for me. You need to reformat the cell for the time, you currently have white Font. You also need Custom NumberFormat "hh:mm"

I am working on it.

sofileen welcome to the forum

Unfortunately your post does not comply with Rule 4 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Yup, that too, Pete

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread