excel - Stuck in infinite loop searching through text boxes -
my code searches through entire worksheet searching text box matches phrase entered. pressing yes goes next response phrase no copy current text box. problem not search through entire work sheet gets stuck looping through 1 worksheet until press no copy. new vba please thankful.
sub findresponse() dim rstart range dim shp shape dim sfind string dim stemp string dim response dim obj new dataobject dim ws worksheet sfind = inputbox("search for?") if trim(sfind) = "" msgbox "nothing entered" exit sub end if set rstart = activecell each ws in worksheets each shp in activesheet.shapes shp stemp = shp.textframe.characters.text if instr(lcase(stemp), lcase(sfind)) <> 0 shp.select response = msgbox( _ prompt:=shp.name & vbcrlf & _ stemp & vbcrlf & vbcrlf & _ "yes see other matches: no copy text", _ buttons:=vbyesno, title:="continue?") if response <> vbyes obj.settext stemp obj.putinclipboard exit sub end if end if end next shp next ws on error goto 0 msgbox "value not found" end sub
the structure of loop this:
for each ws in worksheets each shp in activesheet.shapes shp ... end next shp next ws
as may guess after having had closer @ it, reason stays on same sheet in inner loop, loop through activesheet.shapes
, instead of ws.shapes
, suspect intend do.
i bit surprised hear loop infinite though - it loops through sheet invoke same number of times there sheets in workbook?