G'day all

Can anyone help with the following.

I need to restrict all cells in Column K, so that a maximum of 40 characters
can only be displayed.

I would like to incorporate it into my existing code so that it will trim
any cells in that column automatically if they exceed the limit.

TIA
Mark.

hi, Mark !

I need to restrict all cells in Column K, so that a maximum of 40 characters can only be displayed.
I would like to incorporate it into my existing code
so that it will trim any cells in that column automatically if they exceed the limit.

so... data in cells in column K "comes" from ???

- formulae ? (and you don't mind to loose them ?)
- user direct input ?
- code ?

if user direct input you could use data/validation and restrict the len to 40 characters -?-

or... you could try within your code with something like (adjust to your real address)...

[k2:k38].value = [transpose(transpose(left(k2:k38,40)))]

if any doubts (or further information)... would you please comment ?
hth,
hector.

G'day Hector

There is no user input whatsoever, the data is pasted from another sheet,
once the data is inserted the user clicks the various command buttons that
runs macro's that do a variety of tasks, which all work extremely well.

I tried using this but it didn't quite have the desired effect I was hoping:

Columns("K:K").Select
Selection = Left("K:K",40)

All I got in return was every cell in Column K with K:K

I need to restrict the size of this particular column due to printing
documents. If I keep this column trimmed I can fit the whole sheet onto one
printed sheet at a readable size.

Regards
Mark.

Data Validation Length Between 1 & 40.
Input Message (or Input Message) Title 'Enter less than 40 characters!!!'

More Data Validation tips and techniques he
http://www.contextures.com/xlDataVal08.html
http://www.contextures.com/xlDataVal02.html


Regards,
Ryan---

--
RyGuy


"Héctor Miguel" wrote:

hi, Mark !

I need to restrict all cells in Column K, so that a maximum of 40 characters can only be displayed.
I would like to incorporate it into my existing code
so that it will trim any cells in that column automatically if they exceed the limit.

so... data in cells in column K "comes" from ???

- formulae ? (and you don't mind to loose them ?)
- user direct input ?
- code ?

if user direct input you could use data/validation and restrict the len to 40 characters -?-

or... you could try within your code with something like (adjust to your real address)...

[k2:k38].value = [transpose(transpose(left(k2:k38,40)))]

if any doubts (or further information)... would you please comment ?
hth,
hector.

hi, Mark !

the little and tricky "secret" is the double use of the transpose (worksheet-function) in the code
so, if you don't know the exact dimension of the range to apply the "evaluation" method
you could try using another evaluation "form" as in the following manner:

With Range([k2], [k65536].End(xlUp))
.Value = Evaluate("transpose(transpose(left(" & .Address & ",40)))")
End With

hth,
hector.

__ OP __
There is no user input whatsoever, the data is pasted from another sheet
once the data is inserted the user clicks the various command buttons
that runs macro's that do a variety of tasks, which all work extremely well.

I tried using this but it didn't quite have the desired effect I was hoping:

Columns("K:K").Select
Selection = Left("K:K",40)

All I got in return was every cell in Column K with K:K

I need to restrict the size of this particular column due to printing documents.
If I keep this column trimmed I can fit the whole sheet onto one printed sheet at a readable size.

Regards
Mark.

Thx heaps Hector

That did the trick

this is quicker than the long winded code I eventually put together &
started using.

Thx again

Mark.

thanks to you, Mark, for posting-back your experience
(I really appreciate it)

regards,
hector.

__ OP __
Thx heaps Hector

That did the trick

this is quicker than the long winded code I eventually put together & started using.

Thx again

Mark.