Split text entered in UserForm text box into two cells; split at space within sentence.

Hello All. This is my first post here. I hope my question/request is clear. Please let me know if more information is required.

I have a UserForm to enter text (complete sentences) that needs to be subsequently split into two cells in my workbook. The UserForm textbox has a limit of 104 characters and wordwrap is enforced. I need to "split" the text where the the wrap occurs; at the end of the top line. Splitting a word is not permitted.


I've attached a sample of what I have so far. The VBA includes more functions than addressed in this question. Feel free to either address using the spreadsheet I've attached or as a standalone entry.

Attached: "Alerts Form" command button will show the UserForm. I need the two lines of text to be split into A6 and A7. As it is right now, all text entered into the userform is dumped into A6.

I think one of the "Clear" buttons is not working in this sample. The "Insert" buttons will be removed once this is figured out.

Thanks in advance.