VBA [Excel] Retrieve data from a listbox in a formula
Solved
Alya
-
Alya -
Alya -
Hello,
I am working with Excel 2013 and I need some help.
I have a mixed list of four sports in column A and I would like to be able to select one from the listbox of a userform (without having to write it in a cell) and a msgbox will tell me how many times that sport appears in column A.
Additionally, I would also like to know if the choices in the listbox can be integrated into the code without having to write them in the cells of the sheet.
I tried with the code
but I encountered an error message, here is the link to the file: https://www.cjoint.com/c/JAwoqszGndC
Do you have a solution to suggest?
Thank you very much!
I am working with Excel 2013 and I need some help.
I have a mixed list of four sports in column A and I would like to be able to select one from the listbox of a userform (without having to write it in a cell) and a msgbox will tell me how many times that sport appears in column A.
Additionally, I would also like to know if the choices in the listbox can be integrated into the code without having to write them in the cells of the sheet.
I tried with the code
Private Sub CommandButton1_Click()
Choice = ListBox1.Value
test = WorksheetFunction.CountIf(Range("A1:A10"), Choice)
MsgBox test
End Sub
but I encountered an error message, here is the link to the file: https://www.cjoint.com/c/JAwoqszGndC
Do you have a solution to suggest?
Thank you very much!
1 réponse
Hello Alya,
I replaced your ListBox with a ComboBox and it works with the following code:
Additionally, to integrate the choices of your ComboBox into the code, clear what you have in the RowSource property of the ComboBox, and add the following code:
Enjoy!
I replaced your ListBox with a ComboBox and it works with the following code:
Private Sub CommandButton1_Click()
Choice = ComboBox1.Value
test = WorksheetFunction.CountIf(Range("A1:A10"), Choice)
MsgBox test
End Sub
Additionally, to integrate the choices of your ComboBox into the code, clear what you have in the RowSource property of the ComboBox, and add the following code:
Private Sub UserForm_Initialize()
UserForm1.ComboBox1.List = Array("", "Judo", "Karate", "Kick boxing", "Wrestling", "Taekwondo")
End Sub
Enjoy!
Thank you!!!