Split UserForm textbox entries

luupski

New Member
Joined
Jul 19, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hello All,

Before posting this Question i have searched quite some time, but did not find or come to a solution.
You need to know i'm not a VBA programmer, i find example codes read about it and tinker as long as it takes to get some working for me.
However this time i got nowhere, so hence posting my Question.

I have two tables that i would like to update via UserForm
However the data comes from a database and is when copied, it is pasted as one line in a text box it is separated by spaces like 1122 3344 5566 (for example)
What i would like to to do is use the UserForm to update an existing table when i click a CommandButton called 'Update Tables'.
So add a new row to that existing tables and paste the data from the textbox into the table.
But the data has to be split so 1122 3344 4455 all are pasted in a separate cell.
Or, if updating an existing table is not possible to update an existing list

See attached picture to (hopefully) clarify my question

Thank you in advance for any assistance

Luupski
 

Attachments

  • TableUpdate.jpg
    TableUpdate.jpg
    30 KB · Views: 11

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,436
What part are you having a problem with; the splitting of the text? See the Split function.

VBA Code:
'Split text into an array
MyData = Split(Me.TextBox1.Text)

'Write the array to a Row
Range("A1").Resize(1, UBound(MyData) + 1).Value = MyData
 

luupski

New Member
Joined
Jul 19, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
What part are you having a problem with; the splitting of the text? See the Split function.

VBA Code:
'Split text into an array
MyData = Split(Me.TextBox1.Text)

'Write the array to a Row
Range("A1").Resize(1, UBound(MyData) + 1).Value = MyData

What i am struggling with first is to get one table / list to be updated, then i want to have the two tables updated with a click on the button
 

luupski

New Member
Joined
Jul 19, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
I for
What i am struggling with first is to get one table / list to be updated, then i want to have the two tables updated with a click on the button
I forgot earlier , but thank you for your reply :)

I have search some more and did some tinkering and came up with

VBA Code:
Private Sub CommandButton1_Click()

    nextRow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1
 
  
    arr = Split(Me.TextBox1, Chr(9))
    Sheets(1).Activate
    Sheets(1).Cells(nextRow, 1).Resize(, UBound(arr) + 1).Value = arr
      
End Sub

This does basically what i need, split bthe tabbed text and update this in each cell separately on a new line
But have not found a solution yet to have this updates in a table that starts at J2 for example
 

luupski

New Member
Joined
Jul 19, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
I have a working solution finally (probably a neater code possible for this :) )

This does updates the two tables, Table_A and Table_B at the same time with the tabbed data in the two text boxes and splits this correctly
Table_A starts at Column 9 ("I") and Table_B starts at column 13 ("M")

@ AlphaFrog thnx for helping with the Split function (y)



VBA Code:
Private Sub Update_Tables_Click()


nextRow = Sheets("Sheet3").Cells(Rows.Count, 9).End(xlUp).Row + 1
nextRow2 = Sheets("Sheet3").Cells(Rows.Count, 13).End(xlUp).Row + 1


'Split text into an array
MyData = Split(Me.TextBox1, Chr(9))
MyData2 = Split(Me.TextBox2, Chr(9))


'Write the array to a Row


Sheets(3).Cells(nextRow, 9).Resize(1, UBound(MyData) + 1).Value = MyData
Sheets(3).Cells(nextRow, 13).Resize(1, UBound(MyData2) + 1).Value = MyData2


End Sub


Grtz ... Luupski
 
Solution

Forum statistics

Threads
1,147,517
Messages
5,741,631
Members
423,674
Latest member
Charles2dodo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top