Constantly adding items to a working macro

For my ICT A-Level, I have got to make a working prototype for a client on pretty much anything, my main objective is to make an invoice for a carpenter (which is all fine and working) but i have a sheet where all the materials are and what type of material (cladding, door, kitchen etc) and I use these materials to assign them to a customer on a different sheet. On this sheet it has a material combo box, then when thats selected on the next combo box there will be the product names for that material.
These combo boxes need to be constantly updated automatically when i add new materials to the material list, is there a way of doing this; if so can you explain it in the most basic form as i am a complete novice not had any experience with Excel VBA apart from this project. I've attached the worksheet as it was quite hard to explain my problem, many thanks. (all customer details are fake)

I have to say that what you have done is very impressive. You should be very proud of yourself, and so should your lecturers. IMO, a brilliant start.

I think it would help if you look at creating some Dynamic Named Ranges to refer to your lists. For example, you use:

Please Login or Register  to view this content.

And it could be something like this instead:

Please Login or Register  to view this content.

Where the DNR nrMaterials is defined as:

Please Login or Register  to view this content.

Whenever you add anything to the bottom of that list, it will be visible in the ComboBox(es).


You would adjust other named ranges in the same way so that, when you add anything, it will be included/visible. And, if you need to loop through a range, you would use the DNR rather than a static range.

You could also investigate using Structured Tables as they will automatically include new rows within the Table. Reference is a little different but not difficult.

The only criticism is the way you record data on the Order (Materials) and Order (Employees) worksheets. Although, in your design, the columns will link to the input form, it could be limiting in the long term. For example, what happens if you want to use more than one labourer or maybe a plumber?

Excel works best in simple two dimensional tables. Where you have multiple pairs or groups of entries, processing can become complex. For example, determining the total hours worked for a project will not be a simple case of using SUMIF/SUMIFS on a column of data.

All that said, hell of a start!

Regards, TMS

Have you got any other constructive feedback to give, or any other suggestions in order to make it an better product overall?

In all honesty, no. I think you have done well. You've set your objective, the Invoice, and provided forms to input the necessary data. You've designed the forms, determined where and how to store the data, even arranged for it to be emailed out.

I'm not saying it's perfect but, unless there is a design to work to, there's nothing to compare it to. In the real world, you'd need to discuss the requirements with your client and agree what the client wants/needs. From there, it is an iterative process until what you deliver matches the client's requirements.

Have a look around for Invoice templates and see how they are put together and if there is anything you cam incorporate ... not plagiarise. Just look for ideas.

Again, well done.

Regards, TMS

I would suggest that a) you do not want to send them an Excel Workbook with their invoice as this is editable and b) the safest way would probably be to save the invoice sheet as a .pdf file and attach the PDF file to the email.

Strictly speaking, we're moving on to additional questions. Although the theme is your project, it would be better to start a new thread for each issue that you want to ask for help with.

Regards, TMS