Updating fields using combo box from defined list & userform

[AlexRT]

Hi all,

Firstly, apologies if I explain this badly - this is my first post and I'm new to VBA in general.

I've created the attached spreadsheet, which is designed to add projects to a list. Using code found online, I've created a Userform to allow the addition of rows into the list on the Projects Sheet via the "Add New Project" button on the Welcome! Sheet.

I'd now like a second Userform that allows projects to be updated / deleted. I've had a go at creating this (frmProjEditDel), and managed to get the first combo box to display the list of projects and update if a new one is added. However, I'd now like to be able to pull the information from the other cells in the project rows to enable them to be edited. I've found examples where this is done via VLookUp into text boxes and then allows the user to manually edit the text; however, as all the fields except Delivery Lead were originally inputted via combo lists, I'd like the current option from the lists to be shown when a Project is selected, but these lists to be the only available input options again.

Hope that makes sense, and thank you in advance for any advice.

[sintek]

Hi AlexRT

This will give you an idea to combine it all in one UserForm...
https://www.excelforum.com/excel-pro...ml#post5114047

[AlexRT]

Hi Sintek,

Many thanks for linking me to the thread. I've taken the code from your response and edited it to fit my requirements. I think I'm mostly there, but can't seem to get the Combo Boxes populated, which then means I can't test the other functions. Any thoughts? I've attached an updated version.

[sintek]

Close...Just two edits...

Please Login or Register  to view this content.

Above missing a letter

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Above has number 1 instead of l

Please Login or Register  to view this content.

replace all 1's in combobox list code with l's

Few changes to combobox change

Please Login or Register  to view this content.

Below code...replaced 1's with l's
5 must be 6 ... size of column to populate...

Please Login or Register  to view this content.

Need to make provision for your Intervention detail...i.e ID 5.3...Perhaps you should add another textbox which will be updated automatically with this value which can then be passed to sheet with other values...Instead of having the code in the update as per above...

Please Login or Register  to view this content.

Have not gone through all code but above will get you on the right track...

[AlexRT]

Thanks Sintek!

I feel like it's almost there - v.3 attached with changes! The lists do now populate and initialize is working. However, there's a few issues:

- With the initialize Sub, if there's only one project in the list it returns an error.
- With Clear Form, it does seem to clear the form, but also returns the error "Run-time error '1004': Unable to get the VLookup property of the WorksheetFunction class.
- The final, and probably most problematic issue, is the Intervention Detail aspect. This is currently inputted via a formula, reason being ID5.1 et al are placeholders, and will be changed to unique text-based values. It's difficult for those using the sheet to remember all these values, hence why they just need to input the Outcome and Level and it'll populate automatically using the formula. I was separately hoping to code something that Fills Down that cell once the rest of the cells are inputted, hence why I'd coded the original form as I had. Don't know if this makes things difficult based on how your code works?

Sorry if this is a lot of detail, but I'm really grateful for your assistance!

[sintek]

Hi...

This should solve... as I mentioned earlier in post 4, I incorporated another textbox for Intervention detail which gets populated dependin selections made...
I also deleted all your sheets Data validation as that just serves no purpose...

[AlexRT]

Thanks sintek, working amazingly now. Cheers for all the help.

[sintek]

...............................................
Thanks.gif