PDA

View Full Version : Auto-insert comment, auto insert date and time



rojashan
06-29-2013, 09:57 PM
Hi,

I'm hoping someone can help me.

Attached is my excel file. A brief background of my excel file. I work in a hotel. When a guest fails to check-in on his reservation, we want to put it in a log. The details are the date it was logged (Column D), the room number (Column E), Surname (Column F), First Name (Column G), Company Name (Column H), Check-in DAte (Column I) and the Check-Out Date (Column J), Booking No (Column K). Two departments will act on it. First, is the Reservations Department, they will find out why the guest failed to check in. They will put the reason on the Reservation Notes column (Column L), Reservation Agent who actioned it (Column M), if it is a hotel or guest error (Column N), amount to be charged (Column O), whether we will charge or not (Column P) and whether it has been resolved or not (Column Q). If reservations department has resolved it, then it is Front Office turn to action it. They will put action taken under the Front Desk Notes column (Column R), Front Desk Agent's nae (Column S), and whether it has been resolved or not (Column T). The last column (Column U) is to denote the over all status of the reservation whether it has been sorted (Close) or still needs to be action (Pending).

Here is what I'm hoping to achieve.

1. Under the Reservations Department Section, the reservations agent will put her notes in the Reservations Notes (Column L), when something still needs to be done, they will select "No" in the Resolved? (Column Q). Ex. Column L: "advised travel agent we will charge 1 night. verify if charge to the credit card on file. Travel agent will contact guest.Awaiting call back from travel agent" ; Column Q: "No". So when the travel agent calls back and say to charge it to the card on file, the reservation agent will update the log with add-on comments to the Column L. Ex. "Travel agent called back and said to charge it to the credit card on file" and change the Column Q to "Yes".

I am hoping that the first time the Reservations Agent added notes to the Column L, it will automatically log the date (dd:mm) and time (hh:mm AM). When the reservation agent updated the Column Q from "No" to "Yes", a pop-up box will appear for the reservation agent to put the add-on comment "Travel agent called back and said to charge it to the credit card on file" and the message will be place in Column L just below the previous notes with the new date and time logged.

The date and time of the logged should not change everytime it is being updated.

Ex: Column L

22.06 8:51 AM advised travel agent we will charge 1 night. verify if charge to the credit card on file. Travel agent will contact guest.Awaiting call back from travel agent
23:06 12:00 PM Travel agent called back and said to charge it to the credit card on file


2. Same request for the Front Desk Section. but instead of Column L it is Column R and instead of Column Q it is Column T.


3. When Column Q and Column T is "No", when "Pending" is selected in Column Q, it will highlight the row color RED
When Column Q and Column T is "Yes", when "Close" is selected, it will grade out the entire row.
When Column Q is "Yes" and Column T is "No", when "Pending" is selected it will highlight the row with color RED but the font of the letters for the Front office section (Column R, S, T) will be bold and different font color so that it will show which department needs to act on it. Vice Versa.
When Column Q is "Yes" and Column T is "No", when "Close" is selected, an error message will pop-up that will state "Not yet resolved. Still needs to be actioned. Please review."

Doug Robbins
06-29-2013, 10:08 PM
You did not attach a file. I think that I would be using a userform as the method of selecting the record to be updated and for inputing the data\comments, etc.

rojashan
06-30-2013, 02:16 AM
sorry, forgot to attach the file. please help me.:help

Doug Robbins
06-30-2013, 08:10 PM
Having taken a look at your workbook, I am more convinced than ever that what you want to achieve would be far better done in other ways.

My first choice would be to use an Access Database, but failing that, I would be creating a userform from a combobox on which you would be selecting the record (spreadsheet row) to be updated and the updating would be done by the input of data or checking of checkboxes on the user form and then clicking on a command button on the form the write the data to the spreadsheet. You could have separate tabs on that userform for use by the Reservations and Front Office Staff so they would only have to address the parts of the record that were relevant to them.

rojashan
07-01-2013, 05:06 AM
yeah, unfortunately, i can't use microsoft access. thanks for trying though :)

SamT
07-01-2013, 06:02 PM
rojashan,

It looks like you have a very good starting point.

I do have two questions:

1) You did not mention who (generic who) is initiating (Filling out columns D to K,) the log.

2) You refer to the Front Office filling out info for the Front Desk. Are these the same people at the same location?

As a program developer I need to understand all your terminology so I don't make a foolish assumption.

Pretend that I am a stupid pig farmer that you are talking to, 'cuz I know as much about running a hotel as you do about pig farming. ;)

SamT
07-01-2013, 08:50 PM
Here is a Proof of Concept. There is a button on Sheet2 that will run a data entry for demo.

On the first form, Only the Close and FrontDesk buttons do anything.

On the FrontDesk Form, you can enter any information you want and the Save Entry button will write the data to the July sheet, but not in the correct columns.

I didn't want to put too much time in it until you see if the concept would work for you.

BTW, the Idea behind the Template sheet is that it can be auto-copied for each new month.

Moving the charts, etc to their own sheet makes coding a lot easier, and IMO, makes it easier for management to ignore the raw data, which they will :devil2: Eventually.