MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

User Form

  Asked By: Lambodar    Date: Sep 08    Category: MS Office    Views: 1919

I am currently working on an excel project for a Hotel that has 40
rooms.Basicly I created a workbook has 46 sheets and 40 of them for
the rooms.What I am trying to do is create a command button in the
main page of the project, when I click to this button the customer
check-in form will show...everything is good up to here but my
problem is; as I said there is 40 sheets for 40 rooms with one user
form how can I enter the customers details in apropriate room.For
example room no 101 is vacant and I want to enter the new customer
in to this room how can I to this in one user form instead of
seperate user form for each room.



6 Answers Found

Answer #1    Answered By: Erin Dunn     Answered On: Sep 08

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.

Answer #2    Answered By: Ana Bradley     Answered On: Sep 08

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.

Answer #3    Answered By: Bernard Gutierrez     Answered On: Sep 08

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
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...

Answer #4    Answered By: Vilhelm Fischer     Answered On: Sep 08

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)

Answer #5    Answered By: Kyle Fox     Answered On: Sep 08

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. ?

Answer #6    Answered By: Danny Perkins     Answered On: Sep 08

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

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?

Didn't find what you were looking for? Find more on User Form Or get search suggestion and latest updates.