Filling in invoices using Userforms.

[radgrad]

Today I am trying to figure out how to use VBA in my Excel invoices. I haven't used VBA before so please bare with me I may ask some easy questions. What I am looking to do is set up a Userform to be a text box that goes through a series of questions in order to fill in my invoice that way a majority of it is done to avoid things such as overlooking parts of the invoice/leaving out data.

I have named my cells to start off since I imagine that would make things quicker to reference too, I'm looking to make it ask things such as "Invoice number?", user input into text filed, from there it puts it into cells then moves on to ask " "Company?", "Type","quantity","Hours","Price","Miles","Price","product" and so on an so forth.

When it comes to VBA I have no clue where to begin really. Not looking to be spoon fed, I'd like to be able to eventually figure it out myself, but I am slightly crunched on time to go learning all of it.

Please go to most recent post if you would like to try and help me.

Thank you for your time!

[alansidman]

Here is a nice tutorial on user forms that will get you started.

http://www.contextures.com/xlUserForm01.html

[Winon]

Hello radgrad,

Welcome to the Forum.

Moderation deleted.

[radgrad]

Here is a nice tutorial on user forms that will get you started.

http://www.contextures.com/xlUserForm01.html

Oh my, Thank you very much, a quick scan over that and I can already tell it's going to be useful for me!


@Winon, Apologies for that, I actually just noticed that. Suppose that is why it's a great idea to find the rules before you post!

[Winon]

Hi radgrad,

I shall see it over this time only, you hear me!!! LOL

Thank you for your quick reaction, and enjoy your stay with us.

Regards

[radgrad]

Not a problem and thank you very much.

So I have so far created my userform, added a button the the worksheet to bring it up, and I thought I had it set to where it would input the data entered in the textbox when I click "fill" it doesn't do anything on a plus note at least the close button works.

I'm using the code off of the tutorial I was given (edited of course to where it fits to my need) is that perhaps not the same thing I am trying to accomplish?

[alansidman]

Upload your file so that we can look at it and help you to repair the issue. Make sure to explain what you want to happen.

[radgrad]

Sorry about the long delay between replies. I was away from the office this weekend and had no way of getting back, but I have decided upon more insight that I have a new idea for this. I will explain it here and I want to see if you guys think it would be more of a hassle to get to work, or if it's a good idea.

I have my invoice from A1:H45, with Invoice # (H4) Amount Due (H6),Test Type(A27),quantity (H27) , hours (C37), price per hour (F37), mile (C38), price per mile (F38), Test (C40) ,Test Cost(F40), Test 2 (C41), Test 2 Cost (F41)

I also have a two columns that represent Test Reference # (A30:A36), and Test subject I.D (E30:E36)

What I would like to do (and this is why I am here, to figure out the best way to do so) I am wanting to make it where I can possibly have a menu in which I can pick the test type (there is another worksheet that has our prices and services) and then have it ask the quantity, from there it could take the price from the other worksheet and fill out the Test charges section that shows how many were done and at what price each was done for (C40 and F40 respectively)

I have the rest of it down to a userform for the miles,hours,invoice # but if I could work those into it and have everything filled out with just one pass that would be great. If needed I can upload my invoice template, but wheres the best place to do that?

[alansidman]

You can probably achieve the lookups for the invoice using the Vlookup function.

Click on this link. http://www.techonthenet.com/excel/formulas/vlookup.php

and this link

http://www.techonthenet.com/excel/fo.../2d_lookup.php

[radgrad]

You can probably achieve the lookups for the invoice using the Vlookup function.

Click on this link. http://www.techonthenet.com/excel/formulas/vlookup.php

and this link

http://www.techonthenet.com/excel/fo.../2d_lookup.php

Okay, I looked into Vlookup the other day, but how it worked kind of confused me as I wasn't sure how effectively it could be used for my situation. Currently reading those links and I'll come back here if I need to ask any questions about that.

Edit: Ok, I have one question, all of my prices are save as what is cost for a single run (there are about 40~prices , but it also has the posobility of a rush, which adds an extra fee depending on what kind of test it was) so would I be better off reworking that into a table for Price = Regular, then one line for the added charge?

[alansidman]

You have an option here. You could add it as an extra at the bottom of your invoice or have a check box on your invoice that indicates "Rush" and then have your lookup table with like the following

Data Range

	A	B	C
1	Product	Reg Price	Rush Price
2	ABC	$ 5.00	$ 6.25
3	DEF	$ 10.00	$ 12.50

Then wrap your vlookup statement in an if statement to determine which column to select for the price.

Alan

[radgrad]

You have an option here. You could add it as an extra at the bottom of your invoice or have a check box on your invoice that indicates "Rush" and then have your lookup table with like the following

Data Range

	A	B	C
1	Product	Reg Price	Rush Price
2	ABC	$ 5.00	$ 6.25
3	DEF	$ 10.00	$ 12.50

Then wrap your vlookup statement in an if statement to determine which column to select for the price.

Alan

Ah! Thank you so much, that actually makes sense now! Okay now as a second question ( I could probably figure this out myself after words, but just so I know) These invoices are for digital copy and emailed out to companies so would I be able to have the table on a different worksheet, use the blank invoice worksheet and have it filled using the vlookup, and it maintain those values after being saved ? I imagine this is as simple as what format you save it as, but I figure I would make sure first so I knew if I'd be wasting time trying to get it to work like that, or if I should make the table in the blank invoice on a separate sheet and just have it hidden/locked.

[alansidman]

If I were doing it, I would create a template with the table on another sheet. Fill the invoice as required. Save the invoice sheet only, (not the table on the subsequent sheet) as a .PDF file. In this manner, it cannot be changed and the values appear has numbers and there are no formulas to get changed. Basically, it becomes a hardcoded invoice.

Alan

[radgrad]

If I were doing it, I would create a template with the table on another sheet. Fill the invoice as required. Save the invoice sheet only, (not the table on the subsequent sheet) as a .PDF file. In this manner, it cannot be changed and the values appear has numbers and there are no formulas to get changed. Basically, it becomes a hardcoded invoice.

Alan

Ohkay, that's what I had in mind, I just have to double check and make sure it is ok for us to change it to PDF format, but I imagine that's no problem. Thank you very much for all of your help Alan! You're awesome!

[alansidman]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.