Limiting characters in a cell

[Colin Hayes]

Hi All

I need to limit the amount of characters in a cell to 45 maximum.

If the cell is found to be over the 45 then the cell contents would be
foreshortened automatically.

Does any one know a formula to effect this?


Best Wishes

Drno

 

[Bob Phillips]

Not a formula, but you can do it with event code

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
If Len(.Value) > 45 Then
.Value = Left(.Value, 45)
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[hrlngrv]

Bob Phillips wrote...

Not a formula, but you can do it with event code

....

And nasty users can turn off event handlers either by turning off all
macros (setting security to High would be one way), or running a macro
containing Application.EnableEvents = False .

If only the first 45 chars at most should be used, then formulas
referencing the cell should do so as LEFT(cell,45). It's always safer
to assume that ALL error handling outside of formulas has been
compromised and design downstream formulas accordingly.

 

[Colin Hayes]

Not a formula, but you can do it with event code

Hi

Thanks for that. I really do need this to be a piece of code that I can
embed into an existing macro. Is that possible? The range of cells
affected would be A1 to A60000.

I did try and put this into the View Code area under the tab , but had
no joy I'm afraid.


Best Wishes

Drno

 

[Frank Kabel]

Hi
you may then post (the relevant part) of your existing event macro

 

[Colin Hayes]

Hi
you may then post (the relevant part) of your existing event macro

Hi all

OK I managed to get this working , using LEFT(cell,45) as suggested.

It was rather more straightforward than I thought.

This is the final code :


Dim lrow As Long
With ActiveSheet
lrow = Range("A" & Rows.Count).End(xlUp).Row
'Range("A1:A" & lrow).FillDown
End With

'Limit Column A to a Maximum of 45 characters

Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],45)"
Selection.AutoFill Destination:=Range("B2:B" & lrow),
Type:=xlFillDefault
Range("B2:B" & lrow).Select
Columns("B:B").Select
Selection.Copy
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft


Thanks for your help.


Best Wishes

Drno

 

[Bob Phillips]

That is not what was being suggested. Harlan was suggesting using the LEFT
function was that any formula that referenced your input cells included a
LEFT function, so as to only work on the 45 left-most characters.

The problem here is that after running this code any future input can be
greater than 45 characters. That is why we suggested event code. As Frank
suggested, post the code you had for that so we can see if we can identify
why it didn't work.

--

HTH

RP
(remove nothere from the email address if mailing direct)

Hi
you may then post (the relevant part) of your existing event macro

Hi all

OK I managed to get this working , using LEFT(cell,45) as suggested.

It was rather more straightforward than I thought.

This is the final code :


Dim lrow As Long
With ActiveSheet
lrow = Range("A" & Rows.Count).End(xlUp).Row
'Range("A1:A" & lrow).FillDown
End With

'Limit Column A to a Maximum of 45 characters

Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],45)"
Selection.AutoFill Destination:=Range("B2:B" & lrow),
Type:=xlFillDefault
Range("B2:B" & lrow).Select
Columns("B:B").Select
Selection.Copy
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft


Thanks for your help.


Best Wishes

Drno

 

[Colin Hayes]

That is not what was being suggested. Harlan was suggesting using the LEFT
function was that any formula that referenced your input cells included a
LEFT function, so as to only work on the 45 left-most characters.

The problem here is that after running this code any future input can be
greater than 45 characters. That is why we suggested event code. As Frank
suggested, post the code you had for that so we can see if we can identify
why it didn't work.

Hi

It actually suits my purposes exactly to use the LEFT function at the
very end of the routine and solved my problem for me. As it is the final
act of the code I wouldn't have concerns about future input.

However , I do see your point. I've just tried again with the coding you
suggested and do find that this does work. This limits the input as it
is entered , and would be future-proof. Maybe I was misusing it first
time around , but I find it would be perfectly functional now.

Thanks for all your expert help

Drno