Drop down selection auto format

[Pyrite]

Ok, so here's what I am trying to do.

I am creating a simple booking system for a meeting room. I have created a
grid of 5 rows (Monday to Friday) and 16 columns (0900 hours to 1700 hours in
half hour increments per cell). At first I thought the best way to allow
people to book was to have them select the relevant cells for the day/time
they want and then click a control button with a macro attached that simply
filled the cells with colour and merged them. Another button could be pressed
to unmerge and return to normal (cancel booking). This seems like it is
frraught with possible errors. One I have already encountered is returning
the borders to cancelled cells. If the booking is over multiple cells, no
problem. If it is just a half hour booking though the macro will not run
because it is told to border the 'inside' of the selection as well as the
outline. I'm sure more problems will arise.

I would really like to be able to have 3 drop down boxes, one for the day,
one for the start time and one for the finish time. A user could then select,
for example, (Monday) (0900) to (1200). A control button then confirms that
selection and merges/fills the releavnt cells.

Is this possible?

 

[JLatham]

Certainly is possible. Takes a little setup and preparation, but nothing
approaching nuclear physics or even rocket science (otherwise I'd be in deep
kimchi).

We will use Data | Validation for the 3 drop down lists. Need to set up the
lists first. I put the lists on the same sheet, way over in columns K and L,
starting at row 1 for both. K1:K7 contain names of the week, Sun-Sat.
L1:L17 contain 24-hour format entries for 09:00 to 17:00 in 30 minute
increments.

My validated lists are at A2 (Day), B2 (start time) and C2 (end time).
A2 validation is set to list with K1:K7 being the source of the list,
B2 validation is set to list with L1:L16 being the source list - this lets
them start at any time up to 1/2 hour before the last possible end time.
C2 validation is set to list with L2:L17 as the list source - this prevents
them from being able to end at the earliest possible start time.

A little preparation for our command button and we're almost done. Press
[Alt]+[F11] to open the VB Editor. From its menu, choose Insert | Module
Cut and paste the code below into it, making any changes you want/need as
far as cell references and such. Close the VB Editor.

Now for the 'Verify' button. In the main Excel menu chose View | Toolbars
and chose the Forms toolbar and used the command button from it. Draw the
command button on the sheet and when it asks you about assigning a macro to
it, choose the name of the macro you just added to the module (VerifyEntries
if you didn't change it during your editing) and that's it!

Here's The Code

Sub VerifyEntries()
Dim reservationText As String

'verify that all entries have been made
If IsEmpty(Range("A2")) _
Or IsEmpty(Range("B2")) _
Or IsEmpty(Range("C2")) Then
MsgBox "You must select a Date, a start time and an end time."
Exit Sub
End If
If Range("C2") <= Range("B2") Then
MsgBox "The END time must be later than the Start time."
Exit Sub
End If
' it appears we have a Day chosen, and
' that the end time chosen is later than
' the start time chosen,
'Accept the input
'put them into a variable as a text string
reservationText = Range("A2") & " starting at " & Range("B2").Text _
& " ending at " & Range("C2").Text
'put the reservation entry into some location
Range("A4") = reservationText
End Sub

 

[J Sedoff]

Does your company use Outlook, or the Exchange Server (you could do this with
other online Calendar services like Google's Calendar, Mozilla's
Sunbird/Calendar, etc? You could just as easily (maybe more easily), set up
a public/shared Calendar with the name of the meeting room. That way, people
could sign up for the room through the web client, anyone you want would be
able to access it to see it, some to modify it and you would be able to have
admin rights over the content. It would tell users when it is already taken,
and by whom. Default the location as the meeting room. That way, they can
choose whatever time frame they wish, and you avoid the complications of
heavy coding.

Just a thought,
Jim

 

[Pyrite]

So far so good. I've got all that working. My next question, is it possible
to have it automatically fill relevant cells with colour on my timesheet grid
depending on what selection has been made. For instance it now shows up
'Monday starting at 0900 ending at 0930' when 'Verify' is clicked. Is it
possible to then click another button 'Book' and have the relevant cells on
the grid filled with colour and merged as necessary? This would then make it
possible for the user to check which bookings there were for the week in a
block booking style format.

I am guessing to do this each element of the three drop downs would need to
refer to a cell. E.g. Monday would refer to Row 5 as that is where it is
located in the sheet and 0900 would refer to Column D, 0930 to Column E and
1000 to Column F and so on. This way it could say that Monday 0900 to 1000 is
cells D5 to E5, Tuesday 1000 to 1100 is cells F6 to G6 and so on. Then a
macro would be able to use these references and merge/fill the appropriate
cells to visually display that block of time as being booked.

Certainly is possible. Takes a little setup and preparation, but nothing
approaching nuclear physics or even rocket science (otherwise I'd be in deep
kimchi).

We will use Data | Validation for the 3 drop down lists. Need to set up the
lists first. I put the lists on the same sheet, way over in columns K and L,
starting at row 1 for both. K1:K7 contain names of the week, Sun-Sat.
L1:L17 contain 24-hour format entries for 09:00 to 17:00 in 30 minute
increments.

My validated lists are at A2 (Day), B2 (start time) and C2 (end time).
A2 validation is set to list with K1:K7 being the source of the list,
B2 validation is set to list with L1:L16 being the source list - this lets
them start at any time up to 1/2 hour before the last possible end time.
C2 validation is set to list with L2:L17 as the list source - this prevents
them from being able to end at the earliest possible start time.

A little preparation for our command button and we're almost done. Press
[Alt]+[F11] to open the VB Editor. From its menu, choose Insert | Module
Cut and paste the code below into it, making any changes you want/need as
far as cell references and such. Close the VB Editor.

Now for the 'Verify' button. In the main Excel menu chose View | Toolbars
and chose the Forms toolbar and used the command button from it. Draw the
command button on the sheet and when it asks you about assigning a macro to
it, choose the name of the macro you just added to the module (VerifyEntries
if you didn't change it during your editing) and that's it!

Here's The Code

Sub VerifyEntries()
Dim reservationText As String

'verify that all entries have been made
If IsEmpty(Range("A2")) _
Or IsEmpty(Range("B2")) _
Or IsEmpty(Range("C2")) Then
MsgBox "You must select a Date, a start time and an end time."
Exit Sub
End If
If Range("C2") <= Range("B2") Then
MsgBox "The END time must be later than the Start time."
Exit Sub
End If
' it appears we have a Day chosen, and
' that the end time chosen is later than
' the start time chosen,
'Accept the input
'put them into a variable as a text string
reservationText = Range("A2") & " starting at " & Range("B2").Text _
& " ending at " & Range("C2").Text
'put the reservation entry into some location
Range("A4") = reservationText
End Sub

Ok, so here's what I am trying to do.

I am creating a simple booking system for a meeting room. I have created a
grid of 5 rows (Monday to Friday) and 16 columns (0900 hours to 1700 hours in
half hour increments per cell). At first I thought the best way to allow
people to book was to have them select the relevant cells for the day/time
they want and then click a control button with a macro attached that simply
filled the cells with colour and merged them. Another button could be pressed
to unmerge and return to normal (cancel booking). This seems like it is
frraught with possible errors. One I have already encountered is returning
the borders to cancelled cells. If the booking is over multiple cells, no
problem. If it is just a half hour booking though the macro will not run
because it is told to border the 'inside' of the selection as well as the
outline. I'm sure more problems will arise.

I would really like to be able to have 3 drop down boxes, one for the day,
one for the start time and one for the finish time. A user could then select,
for example, (Monday) (0900) to (1200). A control button then confirms that
selection and merges/fills the releavnt cells.

Is this possible?