• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Form with multiple choice list box

MikeTE

New Member
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!

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:

AlanSidman

Active Member
Your code looks ok. Can you post (upload) a sample of your workbook with the codes for analysis and evaluation testing?
 

MikeTE

New Member
sure, I had to delete most of info so drop downs wont be visible, but I test it several times and everything works except from list boxes multi choice and I can't find out how to code it properly because it keeps rejecting it. can you please help?
 

Attachments

Top