EXCEL VBA - Data Entry with Multi-Selection UserForm List Box -

i'm trying create user form allows pick options , enter data excel sheet. in user form, have list box several answers. have that user can select multiple answers in list box.

if user selects 2 answers, want excel sheet register 2 rows of data. if user selects 3 answers, want excel sheet register 3 rows of data.

basically doing described here: http://www.excel-easy.com/vba/userform.html except in "city preference" listbox, can select multiple choices. want excel sheet create line item each city preference chosen, while holding other selections same.

i'm thinking code this:

for = 1 "total # of items selected in listbox"      emptyrow = worksheetfunction.counta(range("a:a")) + 1      worksheet.cell(emptyrow,3).value = "selected item(i) list box" next 

thanks!

use function return array of selected items:

public function getselecteditems(lbox msforms.listbox) variant 'returns array of selected items in listbox dim tmparray() variant dim integer dim selcount integer          selcount = -1         = 0 lbox.listcount - 1             if lbox.selected(i) = true                 selcount = selcount + 1                 redim preserve tmparray(selcount)                 tmparray(selcount) = lbox.list(i)              end if         next         if selcount = -1             getselecteditems = array()         else:             getselecteditems = tmparray         end if end sub 

then modify code like:

dim selecteditems variant selecteditems = getselecteditems(mylistbox) 'modify line refer listbox name  = lbound(selecteditems) ubound(selecteditems)      emptyrow = worksheetfunction.counta(range("a:a")) + 1      worksheet.cell(emptyrow,3).value = selecteditems(i) next