Best way to build dynamic form to show data from worksheet range and have textbox to add extra data to one of the rows

[Pianostool]

Hi folks. Just in case you're bored.

What's the best way to have a User Form that dynamically shows data from a worksheet, and has an extra blank textbox against each row of data, to allow new Notes to be added?

I have a worksheet with data of client assets in columns A to C, and sometimes D. There may also be Notes in column F.
There are various Asset Types (SAVINGS, OTHER ASSETS, SUPER, etc) and each Type has a blank row between to separate them:

So in this example, the SAVINGS Asset has 2 entries, the others have one each.
The number of entries is dynamic, in that, for example, the SUPER Asset could have another entry added tomorrow, giving it 2 instead of 1 entry.
Also, the "Savings 1" asset in this example has two lots of Notes in columns F & G.

What I'm trying to achieve, is a user form that dynamically shows all these on it and has text box at the end of each entry to allow new Notes to be added. I only need to show on the form, each asset's Details and Balance (just columns A & B), so the user knows the correct asset they want to add the new Notes to. So there needs to be a blank Textbox on each row of the form next to each asset. All this needs to be built dynamically upon opening of the form each time.
Those new Notes that get input by the user, then need to be added to the corresponding row on the worksheet, and into Column F. If there's already Notes in that cell in Column F, I need to move them to the corresponding cell in Column G, and add the new Notes from the form into Column F. This way, the latest Notes are always in Column F, and any previous Notes all get moved over one column.
So in the above example, if they added a new Note to "Savings 1" asset, the current "Latest notes" and "Previous notes" need to get moved one column over (to G7 and H7), and the new Notes from the form added to F7.
If a new Note was added to "Super 1" asset, it would just go into F16. No moving required as there's no previous notes.

I started by trying a List Box form, but I can't get a blank Textbox against each row, so I'm thinking I somehow need it built on the run as a basic User Form???
I'm happy to have all Assets shown on one Page, or a MultiPage form with a Tab for each Asset type (SAVINGS, OTHER ASSETS, SUPER, etc).
I'm also happy to use Named Cells for the Asset Type headings to make it easier to find where each one starts, if it helps. Just need to work out where each one ends (before each blank cell in Column A).

Note - clients will differ, in that some may have all Asset types, others may only have a couple, like just SAVINGS and SUPER, so no hard coding can be done.

Any thoughts and VBA help would be appreciated, just for one Asset type, as I'll be able to copy and adjust for the other types once I get one working.

Thank you.

 

[hungtbatman1]

Why several, a dozen TextBoxes?

I think 1 ListBox is enough. For the sample data, the ListBox has 7 items:

Savings 1
Savings 2
Asset 1
Super 1
Shares 1
OPS 1
Business 1

1 TextBox is enough for the Note. This note is added for the item selected in the ListBox. So if the item "Super 1" is selected, the Note is added to cell F16.

And so you have to attach the Excel file.

 

[Pianostool]

Why several, a dozen TextBoxes?

I think 1 ListBox is enough. For the sample data, the ListBox has 7 items:

Savings 1
Savings 2
Asset 1
Super 1
Shares 1
OPS 1
Business 1

1 TextBox is enough for the Note. This note is added for the item selected in the ListBox. So if the item "Super 1" is selected, the Note is added to cell F16.

And so you have to attach the Excel file.

 

[Pianostool]

Hi *hungtbatman1 - thanks for the quick reply.

Please be patient but I'm somewhat new to user forms. I had a play using a Listbox type of form but could only return the data as a list to the form.

How do you allow one of the items to be selected & then pop up a text box to take the Notes? There seemed no way to interact with the Listbox.

Thanks.

 

[Pianostool]

Sorry *hungtbatman1, I have just worked out how to select items on a Listbox, and I think I can work out now how to have a single Textbox take in the Notes and hopefully return it to the correct cell in the worksheet.

Thanks for the start.

 

[hungtbatman1]

Try it. If you have any new problem, ask