Contents
Project Links
Project Overview:
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 CalendarAdding 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
=Bookings!$H$3,Bookings!$V$3:$Y$5,Bookings!$AE$3:$AH$5,Bookings!$AN$3:$AT$7,Bookings!$V$7,Bookings!$AZ$3:$BG$7
Trend micro key. 2. Name of range: StDate
=Bookings!$M$3
3. Name of range: Rooms
=OFFSET(Lists!$K$6,1,COUNTA(Lists!$K$7:$K$300))
4. Name of range: Description
=OFFSET(Lists!$M$7,COUNTA(Lists!$M$7:$M$100))
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
=Lists!$H$7:$H$18
Add data validation to cell M6
=Lists!$F$7:$F$14
Add data validation to cell V3
=Rooms
Add data validation to cell V7
=Lists!$D$7:$D$10
Add data validation to cell BD3:BD7
=Description 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.
Cell H5
=E12
Cell H7
=StDate+K7 ![]()
Cell M3
=1 & '/' &M5 &'/'& M6
Cell M5
=VLOOKUP(M4,Lists!H7:I18,2,0)
Cell V6
=IF(V4=',',(V4+V5)-1)
Cell AE6
=SUM(AZ3:BC7)
Cell AE7
=(AE3*V5)+(AE5+AE6)-AE4
Cell E12
=H7-(IF(WEEKDAY(H7)=1,6,WEEKDAY(H7)-2))
Cell F12
=E12
Cell G12
=F12+1
Cell H12
=G12 (Select G12 and H12 and copy to BH12)
Cell E10
=E12 and copy E12 to BG10 (custom formatting to day)
Cell E9
=E12 and copy E9 to BG9 (custom formatting to month)
The formulas below reference the room names from the lists sheet.
Cell C13
=IF(Lists!K7=',',Lists!K7) and copy to C40 Adding our First VBA CodeNavigation 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.
Sub Interface_Sheet()
Sheet1.Select End Sub
Sub Booking_Sheet()
Sheet2.Select End Sub
Sub Cost_Sheet()
Sheet3.Select End Sub
Sub Data_Sheet()
Sheet4.Select End Sub
Sub List_Sheet()
Sheet5.Select End Sub
Sub Invoice_Sheet()
Sheet6.Select End Sub Code to Move Forward and Backwards
Clicking this code will continually add seven days to our calendar.
Sub AddWk()
If Sheet2.Range('K7') = ' Then Sheet2.Range('K7') = 7 Else Sheet2.Range('K7') = Sheet2.Range('K7') + 7 End If End Sub
Clicking this code will remove seven days from a calendar.
Sub MinusWk()
If Sheet2.Range('K7') = ' Then Sheet2.Range('K7') =-7 Else Sheet2.Range('K7') = Sheet2.Range('K7')- 7 End If End Sub
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.
Sub Clearme()
Sheet2.Range('Clearit').Value = ' End Sub 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) Test your Calendar and Navigation
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 TutorialOnline Taxi Booking System Software Online
In our next tutorial we will be adding our code for our bookings.
Project Links
The InfoCabs solution connects
|
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |