vba - Program returns message box again and again if 'no' is pressed in message box? -

the functon of program return result of vlookup in same cell in value looked has 2 problems.

1. returns message box again , again if 'no' pressed in message box?

2. don't want message box appear if enter pressed on blank cells?

here code

 sub worksheet_change(byval target range)    dim ret_type integer   dim strmsg string   dim strtitle string   if target.count > 1 exit sub   if not intersect(target, range("a1:a114")) nothing      application.enableevents = false      table2 = sheet2.range("c2:d3")      strtitle = "alert"      strmsg = "combination not found press yes manual entry"       on error resume next         target.value = application.worksheetfunction.vlookup(target.value, table2, 2, false)          application.enableevents = true          if err.number <> 0            ret_type = msgbox(strmsg, vbyesno, strtitle)               select case ret_type                case 7                 activecell.offset(-1, 0).clear               end select          end if          on error goto 0 ''no error, coming default conditions    end if end sub 

1: make sure re-enable events after every change has been made code. otherwise keep relaunching in recursion.

2: check @ beginning of code if target empty.

+1: don't use activecell.offset in macros this; user have clicked cell, , in case, offset makes no sense. use target variable instead.

your code pretty chaotic, , i'm not sure makes total sense @ point. anyway, here working version. see in-line comments points of interest.

i have simplified bit, removing few useless structures.

 sub worksheet_change(byval target range)    dim strmsg string   dim strtitle string    if target.cells.count > 1 exit sub   if target.value = "" exit sub 'don't bother blank cell    if not intersect(target, range("a1:a114")) nothing     application.enableevents = false     table2 = sheet2.range("c2:d3")     strtitle = "alert"     strmsg = "combination not found press yes manual entry"      on error resume next       target.value = application.worksheetfunction.vlookup(target.value, table2, 2, false)       if err.number <> 0         if msgbox(strmsg, vbyesno, strtitle) = vbyes           target.clear 'better offset version; works if user uses tab or mouse click, not enter         end if       end if     on error goto 0     application.enableevents = true 'only re-enable events after having changed want change   end if end sub