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
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
VBA Code:
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