Restricting cell data amount

  • Thread starter Thread starter NoodNutt
  • Start date Start date
N

NoodNutt

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.
Click to expand...

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 here:
http://www.contextures.com/xlDataVal08.html
http://www.contextures.com/xlDataVal02.html


Regards,
Ryan---

--
RyGuy


Héctor Miguel said:
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.
Click to expand...

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.
Click to expand...
 
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 __
 
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 __
 
Back
Top