Limit Characters

**plank** · 08-27-2012, 12:50 PM

Hello

I'm trying to limit the characters in column A to 7 for example:

12345678901242 will become 1234567 or Abcdefghijk will become Abcdefg

I'd rather not use the =LEFT() function or validation because I'm importing the values from a text file and the user will be able to enter their values

Can anyone help with a loop or function for this?

Thanks in advance

**ShosMeister** · 08-27-2012, 01:07 PM

Are you importing by hand or via a macro? If it's the macro, then just fix it on the import step.

If you want to have it done automagically either during the import AND during user input, then you'd need to put an OnChange even trigger but not sure that's what you're looking to do.

If you can provide clearer requirements, it should be easy to do.

**mrice** · 08-27-2012, 01:18 PM

Try pasting this into the appropriate Sheet tab (e.g. Sheet1) in the VBA editor (ALT F11).

Please Login or Register  to view this content.

Adjust the third line as appropriate to get the desired range.

**plank** · 08-27-2012, 07:53 PM

Hi,

Thank you both for your response.

The code above works but as the sheet that I added the code to is created using a macro it wont work if I add a new sheet.
Is it possible to automatically add this code when a new sheet is created so the user won't have to paste the code?

Thanks in advance

**mrice** · 08-28-2012, 02:25 PM

Try pasting this into the ThisWorkbook tab

Please Login or Register  to view this content.

I've made some additional improvements to cope with entries into more than one cell.

**plank** · 08-29-2012, 01:03 PM

Hi,

Thanks for your help, this works perfectly!
Just have a question about the Sh object is it possible to define the sheet you want to use eg Sheet("name") for this type of Change event?

Thanks

**mrice** · 08-31-2012, 03:12 AM

The Sh object is the object on which you have made the change. For instance, if you have edited a cell on A1 on sheet2, the event will fire with Sh being set to sheet2 and Target being set to the range A1 on that sheet. If you want to disable the functionality on certain sheets then you might consider a select statement based upon sheet name or index number.