Populate textbox 2-8 with the data found in textbox1 with "," as the delimiter

[Jcjepot]

HELP: Im trying to figure out how to code a userform. basically , i want to populate textbox 2-8 with the data found in textbox1 with "," as the delimiter.

I have 2 options ,

either i code it to populate directly from the textbox 1 to the following OR just automatically delimit the data in the database.

help please. thank you

Private Sub CommandButton1_Click()
Set sh = ThisWorkbook.Sheets("Database")

Dim Last_Row As Long

Last_Row = Application.WorksheetFunction.CountA(sh.Range("A:A"))

sh.Range("A" & Last_Row + 1).Value = "=Row()-1"
sh.Range("B" & Last_Row + 1).Value = Me.TextBox1.Value
sh.Range("C" & Last_Row + 1).Value = Me.TextBox2.Value
sh.Range("D" & Last_Row + 1).Value = Me.TextBox3.Value
sh.Range("E" & Last_Row + 1).Value = Me.ComboBox1.Value
sh.Range("F" & Last_Row + 1).Value = Me.TextBox4.Value
sh.Range("G" & Last_Row + 1).Value = Me.TextBox5.Value
sh.Range("H" & Last_Row + 1).Value = Me.TextBox6.Value
sh.Range("J" & Last_Row + 1).Value = Me.ComboBox2.Value
sh.Range("K" & Last_Row + 1).Value = Now
sh.Range("L" & Last_Row + 1).Value = Me.TextBox7.Value
sh.Range("M" & Last_Row + 1).Value = Now


Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.ComboBox1.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
Me.TextBox6.Value = ""
Me.ComboBox2.Value = ""
Me.TextBox7.Value = ""

Call Refresh_Data
End Sub

Private Sub CommandButton2_Click()

If Me.TextBox8.Value = "" Then
    MsgBox "Select the Record to Update)"
    Exit Sub
End If

Set sh = ThisWorkbook.Sheets("Database")

Dim Selected_Row As Long

Selected_Row = Application.WorksheetFunction.Match(CLng(Me.TextBox8.Value), sh.Range("A:A"), 0)

sh.Range("B" & Selected_Row).Value = Me.TextBox1.Value
sh.Range("C" & Selected_Row).Value = Me.TextBox2.Value
sh.Range("D" & Selected_Row).Value = Me.TextBox3.Value
sh.Range("E" & Selected_Row).Value = Me.ComboBox1.Value
sh.Range("F" & Selected_Row).Value = Me.TextBox4.Value
sh.Range("G" & Selected_Row).Value = Me.TextBox5.Value
sh.Range("H" & Selected_Row).Value = Me.TextBox6.Value
sh.Range("J" & Selected_Row).Value = Me.ComboBox2.Value
sh.Range("K" & Selected_Row).Value = Now
sh.Range("L" & Selected_Row).Value = Me.TextBox7.Value
sh.Range("M" & Selected_Row).Value = Now


Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.ComboBox1.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
Me.TextBox6.Value = ""
Me.ComboBox2.Value = ""
Me.TextBox7.Value = ""

Call Refresh_Data


End Sub

Private Sub CommandButton3_Click()

If Me.TextBox8.Value = "" Then
    MsgBox "Select the Record to Update)"
    Exit Sub
End If

Set sh = ThisWorkbook.Sheets("Database")

Dim Selected_Row As Long

Selected_Row = Application.WorksheetFunction.Match(CLng(Me.TextBox8.Value), sh.Range("A:A"), 0)

sh.Range("A" & Selected_Row).EntireRow.Delete

Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.ComboBox1.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
Me.TextBox6.Value = ""
Me.ComboBox2.Value = ""
Me.TextBox7.Value = ""

Call Refresh_Data

End Sub

Private Sub CommandButton4_Click()


ThisWorkbook.Save
MsgBox "Data Saved"

End Sub

Private Sub Label1_Click()

End Sub

Private Sub ListBox1_Click()

End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

Me.TextBox8.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 0)
Me.TextBox1.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 1)
Me.TextBox2.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 2)
Me.TextBox3.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 3)
Me.ComboBox1.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 4)
Me.TextBox4.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 5)
Me.TextBox5.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 6)
Me.TextBox6.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 7)
Me.ComboBox2.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 8)
Me.TextBox7.Value = Me.ListBox1.List(Me.ListBox1.ListIndex, 9)


End Sub

Private Sub TextBox8_Change()

End Sub

Private Sub UserForm_Activate()
With Me.ComboBox1
    .Clear
    .AddItem ""
    .AddItem "Male"
    .AddItem "Female"
    
End With
With Me.ComboBox2
    .Clear
    .AddItem ""
    .AddItem "Initial"
    .AddItem "Follow up"
    .AddItem "LFT result"
    .AddItem "Critical Lab"
    .AddItem "Special Endorsement"
End With
Call Refresh_Data
End Sub


Sub Refresh_Data()


Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Database")

Dim Last_Row As Long

Last_Row = Application.WorksheetFunction.CountA(sh.Range("A:A"))

With Me.ListBox1
    .ColumnHeads = True
    .ColumnCount = 13
    .ColumnWidths = "30,60,100,100,60,80,60,50,100,100,100,200,30"
    
    If Last_Row = 1 Then
        .RowSource = "Database!A2:M2"
    Else
        .RowSource = "Database!A2:M" & Last_Row
    End If
    
     
End With
End Sub

 

[SQUIDD]

Hi

So textbox 1 for example may look like "a,b,c,d,e,f,g,h"

And textbox 2 would need to "a", textbox3 would need to be "b" and so on?

Is that correct.

Not looked at you code you posted BTW,

Dave

 

[Jcjepot]

Hi

So textbox 1 for example may look like "a,b,c,d,e,f,g,h"

And textbox 2 would need to "a", textbox3 would need to be "b" and so on?

Is that correct.

Not looked at you code you posted BTW,

Dave

Hi dave,

Yes, thats about it.

 

[NdNoviceHlp]

"a,b,c,d,e,f,g,h" contains 8 items, there are only 7 textboxes 2-8? Dave

 

[SQUIDD]

Hi

So is there always 6 commas.

Anyway, the below should work, but you may want it to be more dynanic?

Also, not sure how you plan to trigger the code.

TextBox2 = Split(TextBox1, ",")(0)
TextBox3 = Split(TextBox1, ",")(1)
TextBox4 = Split(TextBox1, ",")(2)
TextBox5 = Split(TextBox1, ",")(3)
TextBox6 = Split(TextBox1, ",")(4)
TextBox7 = Split(TextBox1, ",")(5)
TextBox8 = Split(TextBox1, ",")(6)

Dave

 

[SQUIDD]

Or

Little more dynamic

Count = Len(TextBox1) - Len(Replace(TextBox1, ",", "")) + 2
For a = 2 To Count
    Me("textbox" & a) = Split(TextBox1, ",")(a - 2)
Next a

 

[Jcjepot]

Hi

So is there always 6 commas.

Anyway, the below should work, but you may want it to be more dynanic?

Also, not sure how you plan to trigger the code.

TextBox2 = Split(TextBox1, ",")(0)
TextBox3 = Split(TextBox1, ",")(1)
TextBox4 = Split(TextBox1, ",")(2)
TextBox5 = Split(TextBox1, ",")(3)
TextBox6 = Split(TextBox1, ",")(4)
TextBox7 = Split(TextBox1, ",")(5)
TextBox8 = Split(TextBox1, ",")(6)

Dave

This one worked!! I added another command button (FILL).
Thank you so much !!! Indeed a lifesaver.

 

[SQUIDD]

Your welcome