Search code examples
excelvbalistboxuserform

Excel VBA Userforms: How do I transfer data from a List Box to a worksheet cell?

I have a ListBox with three options: Cylinder, Ellipsoid, and Frustum. The User chooses one of these and hits the submit button. What I would like to do is write their choice in a cell on my worksheet when the submit button is clicked. How do I do this?

Solution

  • The ListBox Control object will have some name. So if your form is called frm and the listbox object is named lstBox, you would move its selected value to a range, myRange like this:

    myRange.value = frm.lstBox.text

    If you wanted to loop through cells in a range if would look something like:

    Dim cell as range

For each cell in myRange
    cell.value = frm.lstBox.text
next cell

    The above would write the value in the ListBox to each cell in the range.