Announcement

Collapse
No announcement yet.

Create Room Reservation System

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Create Room Reservation System



    Hello,

    Firstly i haven't used excel to a great extent since my college days. So i'm having to re-learn 99.9% of everything i once new...

    I am volunteering for a non profit organization and trying to create a reservation system for the rooms that they have - kind of like hotel software, but in excel (i did a similiar thing in college but for plane seats)

    Please find it attached.

    What i need it to do:

    Copy all of the info from the main page to the guest lit (a new row each time).

    From the guest list to the Gannt chart - i did a few tutorials on dynamic gantt charts using conditional formatting but cannot get them to work when based on data on a different worksheet.

    Finaly is there a way to check for availabilty on any given date?

    if just someone could point me in the right direction, i would be appreciate it so much words can't express!!!

    Best Regards,

    Jamie

    P.S for the calender drop down on the main page, i'm using 'microsoft time & date picker 6.0)

    http://www.2shared.com/file/6521961/...ibsV05b32.html - pop ups on this site

    http://www.easy-share.com/1906519167/RibsV0.5b3.2.xlsm - same file, better website
    Last edited by Goslingjc; July 2nd, 2009, 01:11.

  • #2
    Re: Reservation System

    Have you seen this thread? A downloadable example is available there.
    Reservation Tracking
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

    Comment


    • #3
      Re: Reservation System

      I did see the example a while ago, though you cannot see the code the guy used to create it, the macro's etc, as it was created on an older version of excel.

      And i couldn't modify to my needs as i couldn't find anything to change!

      Comment


      • #4
        Re: Reservation System

        Seems as if you did not go through the entire post, as there are several versions of the workbook available and among the last postings the files contain VBA code. Try again, but look on page-3 for the last uploaded workbook.

        Edit: After taking a look at your file, I suggest restructuring the "Gantt" sheet so that the dates are in a column and room number is on the top row. Reason: you have more rows than columns and it is easier to scroll down (IMO) than across columns. You have a fixed number of room , but many more available dates (well, 365, anyway).

        On your "Booking" sheet, I would use a pop up calendar form and write the date values to a cell range. This is easier, I think, that working with the embedded control. Then it's just simple matter of copying the cell values to the Guest List and a value of "booked" to the Gannt sheet.

        As for checking room availability, you should be able to perform a dynamic lookup using a dynamic column selection if you name the room numbers. See the page for examples and a workbook download. http://www.ozgrid.com/Excel/dynamic-lookups.htm

        Edit2:
        In the attached sheet:
        Deleted
        • embedded controls = not needed
        • junk VBA code/modules (not all, but most)


        Added
        • popup calendar for date selection - see the module for the code
        • Lookup formula to check for room status:
        • in A19 =INDEX(Gantt.Table,MATCH(C11,Dates,0),MATCH(C17,Rooms,0))
        • In C19 =IF(A19=0,"Available","Booked")
        • Conditional Formatting in Gantt sheet for "booked" rooms
        • Several Dynamic Named Ranges: Gantt.Table,Dates, Rooms



        Had lots of problems with Dynamic Named Ranges continually getting corrupted and resorted to used a named cell on "RoomList" sheet to count rows.

        Restructured the Gantt sheet - reversed orientation of Dates and Rooms

        PlaceBooking Code
        Code:
        Sub PostBooking()
        
            Dim msg As String, Ans As Variant
            Dim iRow As Long, iCol As Long
            
            msg = "This room is currently booked for the selected date."
            msg = msg & vbLf & vbLf & "Please select a different room"
            msg = msg & vbLf & vbLf & vbLf & "This booking will not be posted"
        
            With Sheet1.Range("C19")
                Select Case .Value
                    Case Is = "Available" 'OK to place booking
                        With Sheet5 'Guests
                        Dim lCell As Range
                        Set lCell = Sheet5.Range("A65536").End(xlUp).Offset(1, 0)
                        lCell.Value = Sheet1.Range("C8").Value 'Guest Name
                        lCell.Offset(0, 1).Value = Sheet1.Range("I6").Value 'Check In Date
                        lCell.Offset(0, 3).Value = Sheet1.Range("I7").Value 'Check Out Date
                        lCell.Offset(0, 6).Value = Sheet1.Range("I8").Value 'Room Type
                        lCell.Offset(0, 7).Value = Sheet1.Range("I9").Value 'Room No.
                        lCell.Offset(0, 9).Value = Sheet1.Range("I10").Value 'Number of Days
                    End With
                    
                    'show the room as booked on the Gantt Table
                        With Sheet2
                        iRow = Application.WorksheetFunction.Match(Sheet1.[C11], Range("Dates"), 0)
                        iCol = Application.WorksheetFunction.Match(Sheet1.[C17], Range("Rooms"), 0)
                        iRow = iRow + 2 'adjust for header
                        iCol = iCol + 1 'adjust for header
                        .Cells(iRow, iCol).Value = "Booked"
                        End With
                    Case Else
                        Ans = MsgBox(msg, vbInformation)
                End Select
            End With
        
        End Sub
        Code to Reset (clear) Booking Sheet
        Code:
        Sub ResetBooking()
        
        'Clear the entries in the Booking worksheet
        
            Dim msg As String, Ans As Variant
            
            msg = "You are about to clear all entries in the Booking Sheet"
            msg = msg & vbLf & vbLf & "Do you want to proceed with this?"
            
            Ans = MsgBox(msg, vbExclamation + vbYesNo)
            
            Select Case Ans
                Case Is = vbNo
                    Exit Sub
                Case Is = vbYes
                    With Sheet1
                    .Range("C8").ClearContents
                    .Range("C11:C12").ClearContents
                    .Range("C15:C17").ClearContents
                    End With
            End Select
        
        End Sub
        Attached Files
        Last edited by AAE; July 3rd, 2009, 00:32.
        AAE
        ----------------------------------------------------

        Forum Rules | Message to Cross Posters | How to use Tags

        Comment


        • #5
          Re: Reservation System

          I need to create an excel sheet to track the availability of our conference/training room.

          I've simplified your excel sheet and got it to work, but now I need a function where the cell range corresponding to the duration of the meeting will show as booked.

          I have dates under Column A and times of 30 minutes interval (8:00, 8:30, 9:00, etc) on Row 2.

          Any ideas on how I can solve this?

          Comment


          • #6
            Re: Create Room Reservation System

            This is the modified code that I have at the moment...

            Code:
            Sub PostBooking()
            
            
                Dim msg As String, Ans As Variant
                Dim iRow As Long, iCol As Long
                
                msg = "This room is currently booked for the selected date and time."
                msg = msg & vbLf & vbLf & "Please select a date/time"
                msg = msg & vbLf & vbLf & vbLf & "This booking will not be posted"
            
            
                With Sheet1.Range("I16")
                    Select Case .Value
                        Case Is = "Available" 'OK to place booking
                            With Sheet5 'Details
                            Dim lCell As Range
                            Set lCell = Sheet5.Range("A65536").End(xlUp).Offset(1, 0)
                            lCell.Value = Sheet1.Range("I7").Value 'Booked by
                            lCell.Offset(0, 1).Value = Sheet1.Range("I10").Value 'Date
                            lCell.Offset(0, 2).Value = Sheet1.Range("I11").Value 'Start Time
                            lCell.Offset(0, 3).Value = Sheet1.Range("I12").Value 'End Time
                            lCell.Offset(0, 4).Value = Sheet1.Range("I13").Value 'Duration
                            lCell.Offset(0, 6).Value = Sheet1.Range("I8").Value 'Booked for
                         End With
                        
                        'show the room as booked from on the Calendar Table
                            With Sheet2
                            iCols = Application.WorksheetFunction.Match(Sheet1.[C11], Range("Time"), 0)
                            iRow = Application.WorksheetFunction.Match(Sheet1.[C10], Range("Date"), 0)
                            iRow = iRow + 2 'adjust for header
                            iCols = iCols + 1 'adjust for header
                            Cells(iRow, iCols).Value = "Booked"
                            With Sheet2
                            iCole = Application.WorksheetFunction.Match(Sheet1.[C12], Range("Time"), 0)
                            iRow = Application.WorksheetFunction.Match(Sheet1.[C10], Range("Date"), 0)
                            iRow = iRow + 2 'adjust for header
                            iCole = iCole + 1 'adjust for header
                            Cells(iRow, iCole).Value = "Booked"
                            End With
                            End With
                           
                        Case Else
                            Ans = MsgBox(msg, vbInformation)
                    End Select
                End With
            
            
            End Sub

            Comment


            • #7


              Re: Create Room Reservation System

              simplethings,

              Welcome to Ozgrid.
              Please note that posting your questions in thread started by others is known as thread hijacking.
              Always start a new thread for your questions and if it helps to clarify your needs you may include to any other thread.

              This thread is quite old and is being closed.
              AAE
              ----------------------------------------------------

              Forum Rules | Message to Cross Posters | How to use Tags

              Comment

              Working...
              X