Excel Room Booking System Project
- Excel Room Booking System Project
- Project Overview:
- Project Overview Video:
- Download the Template for Excel Room Booking
- Part1 Video _ Creating the Excel Room Booking Calendar
- Part 1 – Creating our Excel Room Booking Calendar
- Adding the Formulas
- Adding our First VBA Code
- Assigning Code to the Three Buttons
- Test your Calendar and Navigation
- Part 2: Our Next Tutorial
In this VBA project I’m going to show you how you can set up a room booking system in Microsoft Excel. These tutorials will demonstrate some of the processes that I would use.
Note: There are four things that you need to keep in mind about this application.
1. This would only be suited to a small booking system such as a bed-and-breakfast or camping site something of that nature. It certainly would not be suitable for a large hotel system. (A relational database would be needed for this because of the data volume)
2. This is not a commercial application. However it does demonstrate many processes that could be used in developing a small room booking system.
3. Initially we will be developing the booking system only. The interface/analysis and invoice sheets will not be included in this project. (Perhaps in a future project depending on the level of interest)
4. This is an advanced tutorial. You should not attempt this tutorial unless you a sound understanding of Excel and VBA. THIS IS NOT A TUTORIAL FOR BEGINNERS.
If you wish to look at a more basic way to carry this out you can go through the tutorial on the link below.
Project Overview Video:
This video demonstrates the complete application with many of its features (not all). It was published quite some time ago but the application remains the same. I will be showing you how to developing the bookings sheet shown this video.
Download the Template for Excel Room Booking
I would recommend that you download the template because it will make it easier for you to complete this tutorial. The references below are designed for this template. So save time and heartache – use the template.
Part1 Video _ Creating the Excel Room Booking Calendar
I would strongly recommend that you watch this video before creating the calendar. It will walk you through how to set up the data validation and the formulas on the bookings sheet. There is a little bit of VBA code that needs to be added and directions four adding that code are also in this video.
Part 1 – Creating our Excel Room Booking Calendar
Adding the Named Ranges
At this stage of our project we are going to need four named ranges. Two will be static named ranges and too will be dynamic. The first to named ranges will be used in our VBA code below. The two dynamic named ranges will be for our data validation.
1. Name of range: Clearit
2. Name of range: StDate
3. Name of range: Rooms
4. Name of range: Description
Here is a link to a dynamic named range tutorial if you are uncertain as to how they are created.
Adding the Data Validation
We have six data validation lists to add, five are for single cells and one is for an array.
Our first data validation needs to be flexible so on the Error tab make sure that you own untick the Show error feature. This will allow the user to override the values.
Do not do this with the other data validation lists.
Notice also that we have used the two dynamic named ranges here.
Add data validation to cell V5
=Lists!$I$7:$I$28 (untick show error)
Add data validation to cell M4
Add data validation to cell M6
Add data validation to cell V3
Add data validation to cell V7
Add data validation to cell BD3:BD7
Adding the Formulas
These formulas should be added to the header at the top of our bookings sheet.
Before adding these formulas make sure that you have selected a month from the drop-down list in your data validation and a year. They are pretty basic formulas but to get the job done just fine.
Note: These formulas go on to the booking sheet at the top of the calendar.
=1 & "/" &M5 &"/"& M6
=G12 (Select G12 and H12 and copy to BH12)
=E12 and copy E12 to BG10 (custom formatting to day)
=E12 and copy E9 to BG9 (custom formatting to month)
The formulas below reference the room names from the lists sheet.
=IF(Lists!K7="","",Lists!K7) and copy to C40
Adding our First VBA Code
The first thing we need to do is set up our navigation block. I have left a set of grouped shapes on the Bookings sheet. Assign each of these macros to their appropriate shape within the group. Right click the shape and choose assign macro and choose the appropriate macro.
When you have done this copy the complete block and paste it onto any sheet that you want to have navigation working on. This will allow you to hide the sheet tabs when your application is finished.
Code to Move Forward and Backwards
Clicking this code will continually add seven days to our calendar.
If Sheet2.Range("K7") = "" Then
Sheet2.Range("K7") = 7
Sheet2.Range("K7") = Sheet2.Range("K7") + 7
Clicking this code will remove seven days from a calendar.
If Sheet2.Range("K7") = "" Then
Sheet2.Range("K7") = Sheet2.Range("K7")- 7
The code below reference is the named range that we created earlier and clears the values in our boxes. You will note that it does not delete the formulas.
Sheet2.Range("Clearit").Value = ""
Assigning Code to the Three Buttons
Right click on the shapes and choose Assign Macro and assign the macros below to the three buttons.
Week- Button (assign the macro MinusWk)
Week+ Button (assign the macro AddWk)
Clear Button (assign the macro Clearme)
You should now be able to test your calendar by moving forward and back in time and also your navigation should work appropriately taking you from one sheet to another.
Do not move forward with this project until you are confident that this is working perfectly.
Part 2: Our Next Tutorial
In our next tutorial we will be adding our code for our bookings.