I am trying to put together an idiotproof way of inputting data and VBA is not my real forte.
I have a block of text that is copied into a textbox and then when the button is pressed I want the text to start appearging in cell A1 and then be split appropriately. I am using the following code which splits the rows based on a linebreak in the textbox entry :
Private Sub CommandButton2_Click() Dim Str As String, a Dim cnt As Integer Dim w() Str = TextBox1.Value a = Chr(10) cnt = UBound(Split(Str, a)) ReDim w(1 To cnt + 1, 1 To 1) For i = 0 To cnt w(i + 1, 1) = Split(Str, Chr(10))(i) Next i ActiveSheet.Range("A1").Resize(i, 1) = w ActiveSheet.Cells.Replace Chr(13), " " ActiveSheet.Cells.Replace "Edit", " " End Sub
However I want to modify it so that it does slightly more, namely :
1. Where there is a tab in the input text, it splits the data into the next column
2. Where there is a line break in the input text, it splits the data into the next row
3. Validates so that if the first word is not "Bob" then it throws an error
Sample data would be eg :
Bob Kate Fred
Jane Eric Terry
Simon Andrew Flora
So that it would appear as :
[TABLE="class: grid, width: 200"]
where the number of input lines is variable . Hope that this makes sense!