Prevent typing past 140 characters

Hi all,

Is there a way to prevent a person from typing past a character limit?

I know that in Data Validation you can set a character limit for the cell, but that doesn't prevent them from typing more than the limit, it just prevents the text from being accepted once they hit enter.
Is there a way to limit the cell input so that once they reach say 140 characters whatever they continue typing doesn't appear?

Or, perhaps once they reach 140 characters the cell/text color changes to red?

This in Worksheet

Please Login or Register  to view this content.

This in Userform

Please Login or Register  to view this content.

for exemple i used 7 characters in first column.
Change the 7 to 140 and target column 1 to target range you want

Kind regards
Leo

Hey Leo,

Thanks for taking the time to make this, I really appreciate it. Unfortunately I'm running into the same problem as before that the person doesn't know that they've reached 140 characters until after hitting the character limit (with the data validation they get an error, with your more elegant solution it just truncates to the character limit).

Is there a way to report "live", as it were, (similar to how Twitter shows characters remaining) so that once the 140 character limit is reached the user can't input any more or there's a visual indicator like text turning red or a separate cell which counts live as the person is typing (i.e. not waiting to see character count until after they've hit "enter").

there is a way, i see for it later this day

cheers

a change in code for CommandButton1, and extra code in TextBox1_Change

Please Login or Register  to view this content.

as before change the 7 to 140 in real file, 3 times

Kind regards
Leo