I am trying to take a standard purchase and sale agreement for purchasing real-estate, and a proprietary form that I have created for evaluating real-estate, and automatically populate the agreement to be printed and mailed. I have spent the day and came up with the linked solution. Does anyone have any thoughts on how to make this more succinct and clean?
Try using Named Ranges on your form that coincide with the field names on your data list. Then it is easy to create a macro that loops through each field name of your data and populates the named range on the form with the value in the selected row and column of the matching field name.
I've also created a field map using a three dimensional array. The array allows you to map field names of a form to non-matching field names on the data list. For example using the same number of fields but this allows you to associate 'FormRangeName1' = 'FieldNameA'...
Typically I'd use 2 workbooks for this with data to a UserForm fill and 3 workbooks for data to a form in another workbook. Book1 for the data, Book2 for the Form display, and Book3 containing the code which would be hidden from view.
You also need some method of navigating your data. Either filling the form from the selected row, entering a value to indicate the row to fill, or using a form control to indicate the row to fill.