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)))

Comments

Post a Comment

Popular posts from this blog

python - Subclassed QStyledItemDelegate ignores Stylesheet -

-
i'm trying subclass qstyleditemdelegate remove focus rectangle in qcombobox . even though i'm calling base implementation of paint function , nothing else, result different. looks if parts of style-sheet influence bounding box of item taken consideration. class pstyleditemdelegate(qstyleditemdelegate): def __init__(self, *args, **kwds): super(pstyleditemdelegate, self).__init__(*args, **kwds) def paint(self, *args, **kwargs): qstyleditemdelegate.paint(*args, **kwargs) what have paint unmodified qstyleditemdelegate ? as suggested tried replacing pyside pyqt4 , works, appears bug. update pyside 1.1.2 1.2.1 result same. unfortunately switch breaks other parts of code, if there no other suggestions i'm going accept answer. edit bug tracked here
Read more

java - HttpClient 3.1 Connection pooling vs HttpClient 4.3.2 -

-
i have used httpclient3.1(java) connection pooling below : import org.apache.commons.httpclient.httpclient; import org.apache.commons.httpclient.multithreadedhttpconnectionmanager; import org.apache.commons.httpclient.params.httpclientparams; import org.apache.commons.httpclient.params.httpmethodparams; import org.apache.log4j.logger; public class httpclientmanager { private static logger logger = logger.getlogger(httpclientmanager.class); private static multithreadedhttpconnectionmanager connectionmanager = new multithreadedhttpconnectionmanager(); private static httpclientparams clientparams = new httpclientparams(); static { try { clientparams.makelenient(); clientparams.setauthenticationpreemptive(false); clientparams.setparameter(httpmethodparams.so_timeout, new integer(30000)); // set so_timeout clientparams.setparameter(httpmethodparams.head_body_check_timeout, new integer(3000)); // head responses ...
Read more

node.js - StackOverflow API not returning JSON -

-
i using node.js script gather data stackoverflow. know responses gzipped, put code in should take care of that. script follows: var request = require('request'); var zlib = require('zlib'); function getstackoverflowresponse(url, callback){ request(url, {encoding: null}, function(err, response, body){ if(response.headers['content-encoding'] == 'gzip'){ zlib.gunzip(body, function(err, dezipped) { callback(dezipped); }); } else { callback(body); } }); } var url = "https://api.stackexchange.com/docs/questions#pagesize=2&order=desc&min=2014-01-04&max=2014-02-02&sort=activity&tagged=apigee&filter=default&site=stackoverflow&run=true"; getstackoverflowresponse(url, function(questions) { console.log(questions); }); instead of getting json output, i'm getting following response: buffer 0d 0a 0d 0a 0d 0a 0d 0a 3c 21 44 4f 43 54 59 50 45 20 48...
Read more