Delimit List from TextBox in UserForm to rows

Hi, I'm brand new to macros and VBA but have been using Excel for a very long time. I'm looking to simplify a tool and calculator I've built with the use of macros and looking to do as follows.

User will run macro and paste their list into the UserForm's TextBox, we'll call it TextBox1. They press the button, CommandButton1, and the macro takes their list of numbers separated by commas and pastes them into a column in separate rows.

Example:

Paste 1,2,3,4,5,6,7,8 in TextBox1

Paste 555,777,999 in TextBox 2

Return

B

C

1

555

2

777

3

999

4
5
6
7
8


I'd like to be able to choose the column they get pasted in as well and this macro will always be run from the same sheet, so calling out the sheet I think wouldn't be necessary but correct me if I'm wrong.

I don't want to do this manually as I'm looking to maximize the adoption of this calculator I've built and making it easier will make it more likely to be adopted. I've been scouring the internet and forums for how to do this but can't seem to get a complete answer and, as I'm novice with VBA in its entirety, I'm finding it rather difficult to piece together.

Hi emurray1324,

Welcome to the forum

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.+

I realize you may not have buttons or code (or forms) yet, but a mockup of desired output would help illustrate it better.

Thanks

I guess changing the post from B1, B2, to B and C helps... the spacing still threw me off a bit, but the file is exactly what I hoped for. Have to run to a meeting, but if nobody beats me to it, I'll upload a file in a bit.

Here is a start... you said you wanted to choose the columns to be pasted, right now I made them textboxes, but let me know what needs tweaking.

Wow I am extremely impressed. Would it be possible to hardcode the columns to paste the data in? This solution definitely works and upon seeing it, I'd like to limit the end-user's interference as much as possible.

EDIT: I was able to just lock a value to them in the TextBox properties. I figure that's the least I'm able to do! Thank you very much for leaving the comments in the code as well, it helps me discern the individual pieces.

Since you don't need code for entering the columns, and just want to be able to edit the code, I have taken out all the validation and message box stuff, as well as the check for string/numeric column entries, it significantly cleans up the code. I have left variables for the columns to make it really easy to update them without reading the rest of the code. Looks better too without unnecessary text boxes/labels.

EDIT: Oops... left in a bit of useless code... delete:

Please Login or Register  to view this content.

Attachment updated, but not sure if you had already grabbed it.