# MS Excel 2007 - creating a pop up form/sheet



## Roamvehawk (May 28, 2010)

Hello everyone!!:wave:

I am currently trying to create an inventory managemnet excel sheet.
Quite simple and moderate for our small gunshop. My problem is something like this:

In the sheet with the guns (S/N, model, type, caliber etc), i want to create a column that will contain a list with two choices "available" or "booked". So far so good. I was thinking about, when you coose "booked" in the list, a new sheet or a kind of form will appear to fill up the details of the customer that booked the gun and the booking details. Should I run into the unknown (at least for me) Macro-Paths or if possible, can it be done wit another way? 

thank you in advance


----------



## RSpecianjr (Jan 20, 2010)

Hey Roamvehawk,

If you attach a sample worksheet we can throw something together for you. Please zip and attach it to a post.

Thanks,

Robert Specian Jr.


----------



## Roamvehawk (May 28, 2010)

Hello Robert my friend,

First of all thank you for your interest.
I have attached a file, also containing some comments.
Take a look and tell me what you think

Thanks for your time


----------



## RSpecianjr (Jan 20, 2010)

Hey Roamvehawk,

Sorry for the delay, I've this server i've been working on has been giving me issues and has been consuming most my time... 

I've attached a worksheet with a macro/form in it. Hopefully its what you are trying to get done. Heres the just of it:

When someone clicks on a cell that has "Booked" in it, then it will pull up the form with the following customer information:

Name, Amount of Deposit, Date of Deposit and Telephone number.

If in column H the cell is "Available" or is empty, nothing happens.

If the user switches it to "Booked", the form pops up and the user can input the information.

There are three choices on the form. Okay, Clear All, and Cancel.

Okay submits the information so you can pull it up later. Clear All, clears the information for that showcase. Cancel makes no changes and closes out of the form.

There is only two worksheets in the Workbook. One for tracking the inputted information from the forms, and the main sheet with the showcases on it.

Let me know if this is what your looking for. It is a very simplistic form and you could probably benefit from a bit more functionality... For instance a calendar for the Date of Deposit, or limiting the fields for telephone number and amount of deposit to Numbers only. I just don't have the time at the moment.

Hope this helps,

Robert Specian Jr.


----------



## Roamvehawk (May 28, 2010)

Hell Robert and thanks a lot for the help.

I have just checked your work. It sure is close to what I imagined.
I think I can start editing stuff like the calendar etc.
There only one thing that got me...
When I "select all" or select whole row, or select whole column,
I get a "run time error 13 - type mismatch"

Any Ideas on how to correct this??

Also is it possible to move the sold items on "sheet 2" ?

Thanks again


----------



## RSpecianjr (Jan 20, 2010)

Hey Roamvehawk, 

Are booked items considered sold? I added another column with a validation for sold or not sold. This will determine whether it gets moved over or not. I also Added the second Worksheet for Sold Items and fixed the error 13 that you were getting.

Let me know if this is what your looking for. = )

Regards,

Robert Specian Jr.


----------



## Roamvehawk (May 28, 2010)

Very nice work Robert!!

Its a very nice template you gave me, to start personalizing in the Greek version...

I hope I will be able to understand some of the VB and make some changes

Thank you!!


----------



## RSpecianjr (Jan 20, 2010)

Thanks Roamvehawk!

There are a few different bits of code. There is code under each:

Worksheet 1 object
worksheet 2 object
Userform object

If you need any other help just let us know!

Regards,

Robert Specian Jr.


----------



## neekstylez (Jul 28, 2010)

Just wanted to say thanks as I was able to use this as well!


----------



## RSpecianjr (Jan 20, 2010)

Your welcome neekstylez. If you have any problems adjusting it to your needs let us know and we can fix it right up.

Regards,

Robert D. Specian Jr.


----------

