arrays - VBA Live-filter listbox via textbox & save multiple selections from listbox in one cell -

hello again community,

after got last problem, promted me rework entire code in more efficient manner, ask 2 more questions regarding same project.

(1) implement live-filter in listbox cglist1, connected textbox searchcglist1. whenever types in textbox, results in listbox should adjusted. found article on website, article 3 on external webpage. however, due limited skills, have not been able adapt properly. more later.

(2) after multiple items same listbox cglist1 have been transferred second listbox cglist2 via button (which works treat), save them in same cell (range "bm") on worksheet meta db. problem used google extensively , tried adapt findings (see links below) code - without success.

i hope patient ones amongst can me out once again, in knowledge trying learn as possible. problem lot of things, not know for.

my preliminary code problem 1:

cglist1 , cglist2 have no code. populated in userform_initialize sub via:

'fill material groups listbox1 dynamically dim cell range dim rng range  thisworkbook.sheets("commodity groups")     'range 500 in order allow further additions     set rng = .range("a2", .range("a500").end(xlup)) end  me.cglist1.columnwidths = "20;80"   each cell in rng.cells 'filter out blanks if cell <> ""     me.cglist1         .additem cell.value         .list(.listcount - 1, 1) = cell.offset(0, 1).value     end end if next cell 

i cannot use .additem , filter through columns find in many examples online because needs dynamic , there many blanks in between selection items on worksheet.

the buttons:

private sub addcgbutton_click()  = 0 cglist1.listcount - 1     if cglist1.selected(i) = true         'copy cg name, not respective number/letter combination (only more work cut out when working later)         cglist2.additem cglist1.list(i, 1)     end if next  end sub  'delete selected commodity groups list 2 re-selection private sub delcgbutton_click()  dim counter integer counter = 0  = 0 cglist2.listcount - 1     if cglist2.selected(i - counter)         cglist2.removeitem (i - counter)         counter = counter + 1     end if next  end sub 

after lot of trial , failure trying adapt linked approaches other people, tried more simple:

private sub searchcglist1_change()  'only show textbox matching items in cglist1 (filter)  dim strsql string  strsql = "select fieldname table fieldname = " strsql = strsql & "'" & me!searchcglist1 & "*'" strsql = strsql & " order fieldname;"  me!searchcglist1.rowsource = strsql  end sub 

but without success.

regarding problem 2:

to save multiple selections cglist2 in range bm on worksheet "meta db", toyed around lot , last try was:

save multiple selections commodity group list 2 same cell in excel dim listitems string, c long  cglist2     c = 0 .listcount - 1         if .selected(c) listitems = listitems & .list(c) & ", "     next c end  range("bm") = left(listitems, len(listitems) - 2) 

usually, other userform entries saved single command button in following fasion:

private sub commandbutton21_click() 'application.screenupdating = false   'define relevant wbs working dim wbinput workbook dim wb workbook set wb = activeworkbook dim ws worksheet set ws = wb.worksheets("meta db") dim lastrow long   'save userform inputs ws     .activate     lastrow = ws.range("a" & rows.count).end(xlup).row + 1     .     .     range("bk" & lastrow).value = me.payinfo90     range("bl" & lastrow).value = me.payinfo90more     'risk management - residual information     range("bm" & lastrow).value = me.cglist2     range("bn" & lastrow).value = me.suppsince     .     . end  end sub 

again, thank took time read post , answer tips on improve.

everyone have great day.

using helper column array formula.

so if had data 1st list box in a1:a10 , selection listbox placed in d1, 2nd complete listbox selections in b1:b10, not used, in e1:e10, have following array formula filled down, populate 2nd listbox off helper column e.

beginning =index($b$1:$b$10,small(if(left($b$1:$b$10,len($d$1))=$d$1,row($b$1:$b$10),""),rows($e$1:$e1)),1)

containing =index($b$1:$b$10,small(if(not(iserr(search($d$1,$b$1:$b$10))),row($b$1:$b$10)),rows($e$1:e1)),1)

you need press ctrl shift , enter array formula.