to cut a long story short... have you considered the benefits of using a
MS Access database instead?

I think it would be infinitely simpler in this instance (one record per room
in a table(s) etc).

Access could handle this in a single form  very easily.

Access is an "extra" in that you need to pay for it. Standard versions
of offie do no include it. It could be a money thing.

To be honest in the begining of the project  I was thinking the same
as you, but the client wants to see a visual rooms as well whenever
they wish so for that reason I had to create  a single sheet for each
room.
If I cannot find a solution for this problem, then I am going to
create a main page  for the all rooms and put a 40 command  button
with the Check-in lebel and create a 40 user  form for every each
room...I don't know this sounds bit silly but that's all I can do...

Let's say your sheet naming is in the form: "Room101" and hat your form  has
a field called intRoomNumber. You can select the sheet you need in VBA using
something like:

strSheetName = "Room" & str(intRoomNumber)
Sheets(strSheetName).Select

I think you could still show  a visual listing of the rooms without
having 40 sheets, or 40 buttons. Take a look at setting up a user  form
that will select the room for a combo box, and then perform an Advanced
Filter on the button  click. That way, you can filter out the other
rooms, have the data in a better format for manipulation and still meet
the client's needs. ?

I agree with that concept. Your reason for not
liking that concept was because your client wanted to see a "visual"
picture of the rooms.

I think you can accomplish that, by using the AutoFilter feature of
Excel.

What I would do is create  a UserForm that has all the rooms listed, the
client can open the UserForm with a button, and select from the dropdown
the room that he/she is interested in seeing information about. When
the client clicks the OK button, an AutoFilter executes to display only
the necessary information about "Room101", because you have passed the
parameter of Room101 to the AutoFilter command  via code. This way, you
are using Excel more like a database.

Does that help?