Cost Estimator User Forms Excel VBA

This video captures a small portion of an exciting and interesting freelance gig I finished in August on the UpWork platform. The whole tool is a custom cost estimator built with Excel and Excel VBA. Kudos to Laura and Ken for all the work on this before I was involved!
Among other data-related, automation tasks that were required, there were two user forms that needed to be designed and built out to cover a couple pretty creative requests.
The first form triggers with a right-click on a cell and allows the user to generate a series of line items with which to create, and insert, the final value into the target cell. These "background" data are stored and can be recalled and edited as needed.
The second form allows for modification of the template by inserted multiple subcodes in a structure underneath specific parent codes - an upgrade to the one-at-a-time process they were previously using.
Post any questions or comments below... More Excel, Access, and PowerPoint videos are in queue to share. Stay tuned!

[@aggiebq86]

You did a great job with this. I'm working on building a cost estimating template myself. Not quite as detailed as this but with some of the same issues. I have a master unit price list for various task. I want to build a cost estimate template using a pulldown menu to select tasks which would then pull the standard description and unit pricing into my estimate. I'd prefer not to use a VLOOKUP formula for pulling in the unit price because I want to have the ability to override the unit price where needed due to anticipated increased/decreased productivity. Could you point me in the right direction on how to achieve this?

[@cdtcsllc]

Hello, @aggiebq86t. Thanks for your question. Without using a function, you'd need to get VBA involved. I'm assuming when you say "pulldown", you're referring to a drop-down set up through Data Validation that appears on multiple rows? (i.e., there would be a drop-down in each row for this selection?). If I'm on-track so far, in VBA, you can use the Worksheet_Change event to trap the value that was selected in the drop-down (along with the cell address, etc.). Have that process load a small userform with the selected item's values in it, and allow the user to override the default unit price (or keep the default). The value(s) selected in the form would then populate the cells adjacent to the selected value. Off the top of my head, that's the direction I would take.
Frankly, I've been moving more complex builds from Excel into Access as a *lot* of functionality for various selection types, value overrides, userform flexibility, etc. is much better and "baked in". Done correctly, the user would hardly know they're working in Access. My two cents.

[@aggiebq86]

@@cdtcsllc Yes, I meant the drop-down function. I think this is the route to go. Then I could also modify the description if needed to more accurately describe the task or material along with adjusting the unit price if needed.
How would I be able to enter a "One Off" code for something that isn't in the master price list dropdown menu? It wouldn't have to add the one off item to the master price list, just give me the ability to input a description and unit pricing for an item into my estimate.

[@samuelniyokwizerwa3170]

PLEASE LETS CONNECT, NEED MORE DETAILS

[@cdtcsllc]

Hi, Samuel. Feel free to reach out via [e-mail], or, this video is actually part of my Upwork freelancing profile, and you can find me there (on Upwork) at www.upwork.com/freelancers/cdtcsllc . Happy to chat with you.