score:2

Accepted answer

when a user fills in a cell that sends the range over the 1000 character limit, it will call application.undo which should just remove the last text that they added.

option explicit

private sub worksheet_change(byval target as range)

    if not intersect(target, range("a5:a30")) is nothing then

        dim charcount as long

        dim arrvalues as variant
        arrvalues = range("a5:a30").value2

        dim i as long
        dim tempsplit as variant
        dim j as long

        for i = lbound(arrvalues) to ubound(arrvalues)
            tempsplit = split(arrvalues(i, 1), " ")

            for j = lbound(tempsplit) to ubound(tempsplit)
                charcount = charcount + len(tempsplit(j))
            next j
        next i

        if charcount > 1000 then
            with application
                .enableevents = false
                .undo
                .enableevents = true
            end with

            msgbox "adding this exceeds the 1000 character limit"
        end if

    end if

end sub

Related Query

More Query from same tag