Macro to replace line of data within a table

Hi all,

I have a problem where I've created a sizeable table of data that creates short range programs and workflow trackers, but the end user that manipulates the table has limited excel skills and understanding which of course then effects any worksheets/formulas that come off this table.

I had an idea where I could create a user form that looks up the content of one line in the data table but displays it in a much more user friendly manner. The end user could then type over this to update the contents of the table by pressing a button(macro) that transfers the new content of the user form to appropriate line in the data table. There is a third sheet that mirrors the data table exactly but then is just equal to the cells in the user form - so this would be the one to update the data table with.

I've attached a sample workbook that I think would better explain it but any help would be greatly appreciated as this is causing a bit of a headache.

Many thanks,

Jason

Hi there,

This looks interesting, but it's definitely not a five-minute job!

I'll take a look at it and see if I can sort something out for you - no promises though!

Regards,

Greg M

Hi there,

Take a look at the attached workbook and see if it does what you need.

The User Form worksheet is protected. The user can select a name from a dropdown list, and can enter data into the 16 Detail and the 16 Content fields. The user can then click a button to have all data additions/changes/deletions sent to the Data worksheet.

The User can not interfere with the values of either the Name or the Code.

The workbook has been set up to cater for 100 rows of data on the Data worksheet.

When a new Name is selected from the dropdown list, named formulas are entered automatically into the Detail and Content cells, and the formulas are then overwritten with their results, so that the user sees data (rather than formulas) in the cells.

When the User clicks on the Save data button, the contents of the Detail and Content fields are copied to the appropriate cells on the Data worksheet.

Hope this helps - please let me know how you get on.

Best regards,

Greg M

That's mightiliy impressive - exactly what I had in mind! Had a look at some of the code you've used for the Macros and now I'm hoping I'll be able to decipher this to apply to my actual workbook. VB is really not my strong point - are there any web links you could point me to that would help me learn how to do specifically this? Never know where to start when it comes to learning all this.

Hi again,

Many thanks for your feedback - I'm very pleased that I was able to help you.

It's a bit difficult to point you towards a "starting location" for learning VBA, and (at the risk of sounding patronising ) some of the techniques I've used (e.g. named formulas) can be a bit complicated.

Have a look through the various named ranges (Formulas > Name Manager) and see if you can relate any of them to the VBA Code - this might give you a general idea of how things are working. If you think I can help you understand any specific items, just ask and I'll see what I can do.

Sorry I don't have a "Learn VBA Magic Wand" to lend you.

Best regards,

Greg M

No not patronising at all - I get it! Have tried some of the obvious places like the Excel for dummies All-in-one e-book but it just contains swathes of information whereas I think I learn better by a copying a process and re-applying to my needs. This will be a great help though and I'll pour through it to see what I can pick up. If I can master this then it would make my life a lot easier as I basically replicate that same template and adapt for most things I do. User Form > Base Data > Reports

Many thanks!