Prev: drop info auto from sheet 1 to 2
Next: Data Validation lists update orginal cell with list update
From: NoodNutt on 3 Jul 2008 23:20
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.


From: H�ctor Miguel on 3 Jul 2008 23:37
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.


From: NoodNutt on 4 Jul 2008 00:17
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.


From: ryguy7272 on 4 Jul 2008 00:20
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" 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.
>
>
>
From: H�ctor Miguel on 4 Jul 2008 00:42
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.


 |  Next  |  Last
Pages: 1 2
Prev: drop info auto from sheet 1 to 2
Next: Data Validation lists update orginal cell with list update