hello experts,
can you please help me with VBA code for list boxes?
everything is running fine until I try to implement code for list boxes 1 & 2. the data is just not feeding through to the spreadsheet table.
can you please help? see my code below. I'll appreciate any help!
can you please help me with VBA code for list boxes?
everything is running fine until I try to implement code for list boxes 1 & 2. the data is just not feeding through to the spreadsheet table.
can you please help? see my code below. I'll appreciate any help!
Code:
Private Sub CommandButton2_Click()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("2019-2020 Tracker")
Dim n As Long
Dim Target As Worksheet
Dim LastRow As Long
Dim lastRowD As Long
If Application.WorksheetFunction.CountIf(sh.Range("F:F"), Me.TextBox3.Value) > 0 Then
MsgBox "Case already recorded", vbInformation
End If
n = sh.Range("B" & Application.Rows.Count).End(xlUp).Row
sh.Range("A" & n + 1).Value = Me.TextBox5.Value
sh.Range("B" & n + 1).Value = Me.ComboBox11.Value
sh.Range("C" & n + 1).Value = Me.ComboBox4.Value
sh.Range("D" & n + 1).Value = Me.ComboBox5.Value
sh.Range("e" & n + 1).Value = Me.TextBox2.Value
sh.Range("f" & n + 1).Value = Me.TextBox3.Value
sh.Range("g" & n + 1).Value = Me.TextBox4.Value
sh.Range("h" & n + 1).Value = Me.ComboBox66.Value
sh.Range("j" & n + 1).Value = Me.ComboBox77.Value
sh.Range("k" & n + 1).Value = Me.ComboBox80.Value
sh.Range("l" & n + 1).Value = Me.ComboBox81.Value
sh.Range("m" & n + 1).Value = Me.TextBox6.Value
sh.Range("n" & n + 1).Value = Me.TextBox7.Value
sh.Range("o" & n + 1).Value = Me.TextBox8.Value
sh.Range("p" & n + 1).Value = Me.ListBox1.Value
sh.Range("q" & n + 1).Value = Me.ListBox2.Value
sh.Range("r" & n + 1).Value = Me.TextBox9.Value
sh.Range("s" & n + 1).Value = Me.TextBox10.Value
sh.Range("u" & n + 1).Value = Me.TextBox11.Value
sh.Range("v" & n + 1).Value = Me.TextBox12.Value
sh.Range("w" & n + 1).Value = Me.ComboBox82.Value
sh.Range("x" & n + 1).Value = Me.TextBox13.Value
lastRowD = sh.Cells(sh.Rows.Count, "B").End(xlUp).Row
Me.TextBox5.Value = ""
Me.ComboBox11.Value = ""
Me.ComboBox4.Value = ""
Me.ComboBox5.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.ComboBox66.Value = ""
Me.ComboBox77.Value = ""
Me.ComboBox80.Value = ""
Me.ComboBox81.Value = ""
Me.TextBox6.Value = ""
Me.TextBox7.Value = ""
Me.TextBox8.Value = ""
Me.TextBox9.Value = ""
Me.TextBox10.Value = ""
Me.TextBox11.Value = ""
Me.TextBox12.Value = ""
Me.ComboBox82.Value = ""
Me.TextBox13.Value = ""
MsgBox "Done!"
End Sub
Private Sub COMBOBOX4_CLICK()
Dim X As Integer
X = ComboBox4.ListIndex
Select Case X
Case Is = 0
ComboBox5.RowSource = ""
Case Is = 1
ComboBox5.RowSource = ""
Case Is = 2
ComboBox5.RowSource = ""
Case Is = 3
ComboBox5.RowSource = ""
Case Is = 4
ComboBox5.RowSource = ""
Case Is = 5
ComboBox5.RowSource = ""
Case Is = 6
End Select
End Sub
Private Sub UserForm_Initialize()
ComboBox4.RowSource = "VBA_RANGE"
With Me.ComboBox81 ''''Formal / Informal''''''
.AddItem "Formal"
.AddItem "Informal"
End With
With Me.ComboBox80 ''''Case Type''''''
.AddItem ""
.AddItem ""
.AddItem ""
End With
With Me.ComboBox82 '''' Category ''''''
.AddItem "J"
.AddItem "R"
.AddItem "S"
End With
With Me.ComboBox11 ''''status''''''
.AddItem "Live"
.AddItem "On hold"
.AddItem "Closed"
End With
With Me.ComboBox66 '''''grade''''''
.AddItem "01"
.AddItem "02"
.AddItem "03"
.AddItem "Other"
End With
With Me.ComboBox77 '''''GENDER''''''
.AddItem "Male"
.AddItem "Female"
.AddItem "Non-Binary"
.AddItem "Other/Prefer not to say"
.AddItem "Unknow"
End With
End Sub
Last edited by a moderator: