excel - how to split multiple values in a cell and link to checkboxes in userform -

hi have following code searches surname , returns values in textbox. want checkboxes checkmark depending on column 6 (f.offset(0,5)). when use code below, it's not picking multiple values in cell in column 6. can pick first one. how can fix this?

private sub search_click() dim name string dim f range dim r long dim ws worksheet dim s integer dim firstaddress string dim str() string  name = surname.value  ws  set f = range("a:a").find(what:=name, lookin:=xlvalues)  if not f nothing   me     firstname.value = f.offset(0, 1).value     tod.value = f.offset(0, 2).value     program.value = f.offset(0, 3).value     email.value = f.offset(0, 4).text     officenumber.value = f.offset(0, 6).text     cellnumber.value = f.offset(0, 7).text  str() = split(f.offset(0, 5), " ")  = 0 ubound(str)  select case ucase(trim(str(i)))  case "pact": pact.value = true  case "princerupert": princerupert.value = true  case "montreal": montreal.value = true  case "tet": tet.value = true  case "wpm": wpm.value = true  case "tc": tc.value = true  case "us": us.value = true  case "other": other.value = true  end select 

edit: i've used code add names column 6

private sub commandbutton1_click() msgbox "directorate has been added", vbokonly   dim ctrl control   each ctrl in userform1.controls     if typename(ctrl) = "checkbox"      'pass checkbox subroutine below:     transfervalues ctrl     end if   next  transfermastervalue   sub transfermastervalue() dim allchecks string dim ws worksheet 'iterate through checkboxes concatenating string of names each ctrl in userform1.controls  if typename(ctrl) = "checkbox"     if ctrl         allchecks = allchecks & ctrl.name & " " 'the names of checkboxes separated spcae in between them         debug.print allchecks      end if  end if next  'if have @ least 1 transfer master sheet if len(allchecks) > 0 'your code transfer  set ws1 = sheets("master")  emptyrow = worksheetfunction.counta(range("a:a")) + 1    ws1         .cells(emptyrow, 1).value = surname.value         .cells(emptyrow, 2).value = firstname.value         .cells(emptyrow, 3).value = tod.value         .cells(emptyrow, 4).value = program.value         .cells(emptyrow, 5).value = email.value         .cells(emptyrow, 7).value = officenumber.value         .cells(emptyrow, 8).value = cellnumber.value         .cells(emptyrow, 6).value = left(allchecks, len(allchecks) - 1) 'to add column 6 

edit 2:

this how it's shown when run debug.print allcheck above add names column 6

pact princerupert  pact princerupert montreal  pact princerupert montreal wpm  pact princerupert montreal wpm tc  pact princerupert montreal wpm tc tet  pact princerupert montreal wpm tc tet  pact princerupert montreal wpm tc tet other  

edit 3: https://www.dropbox.com/s/36e9fmbf17wpa0l/example.xlsm

you're running select on upper-cased values, individual case items mixed-case. "princerupert" won't match "princerupert"

either don't upper-case select item, or change case terms upper-cased.

edit - if it's still not working need check what's being fed select. add line shown below , see produces (will show in immediate pane)

for = 0 ubound(str) debug.print trim(str(i))  '<< add select case ucase(trim(str(i)))