excel - Cell mask format -

i'm trying develop mask cells requirement needs text or numerals.

i have clean button reset respective cells. that's problem. mask checking macro working if when use clean button , empty, same checking macro blank isn't accepted.

following code:

private sub worksheet_change(byval target range)     on error goto whoa      application.enableevents = false       if not intersect(target, range("c5")) nothing           if not isnumeric(range("c5").value)             msgbox "valor inválido."             application.undo             goto letscontinue         end if          range("c5").value = "" & format(range("c5").value, "")     end if      if not intersect(target, range("c7")) nothing         'apaga se nao o numero         if not isnumeric(range("c7").value)             msgbox "valor inválido."             application.undo             goto letscontinue         end if          range("c7").value = "'" & format(range("c7").value, "")     end if      if not intersect(target, range("i9")) nothing          'apaga se nao o numero         if not isnumeric(range("i9").value)             msgbox "valor inválido."             application.undo             goto letscontinue         end if       end if       if not intersect(target, range("i11")) nothing          'apaga se nao o numero         if not isnumeric(range("i11").value)             msgbox "valor inválido."             application.undo             goto letscontinue         end if       end if        if not intersect(target, range("i13")) nothing          'apaga se nao o numero         if not isnumeric(range("i13").value)             msgbox "valor inválido."             application.undo             goto letscontinue         end if       end if        if not intersect(target, range("e15")) nothing          'apaga se nao o numero         if not isnumeric(range("e15").value)             msgbox "valor inválido."             application.undo             goto letscontinue         end if             end if  if not application.istext(worksheets("formulário").range("c9")) msgbox "valor inválido."             application.undo             goto letscontinue end if    letscontinue:     application.enableevents = truea     exit sub whoa:     msgbox err.description     resume letscontinue end sub  function isalpha(str string) boolean dim c string dim integer      = 1 len(str)         c = mid(str, i, 1)          select case asc(c)              case 32, 65 90, 97 122                 isalpha = true             case else                 isalpha = false         end select          if not isalpha exit     next end function 

try following routine instead, using vba-style regular expressions test rules:

private sub worksheet_change(byval target range)  if intersect(target, union(range("c5"),range("c7"), range("i9")) nothing exit sub  application.enableevents = false  if not target.value "[a-za-z0-9]+"     msgbox "valor inválido."     application.undo end if  application.enableevents = true  end sub 

then in macro "cleaning" cells, make sure use application.enableevents = false @ beginning , application.enableevents = true @ end, stops events firing, mean above code isn't run when macro changes value of cell.