MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How do I limit the numbers of characters in a cell?


Posted by Vanaja on January 25, 2001 7:19 PM

Hi

I want to limit the numbers of charaters that can be entered
into a cell to 255.

How do I do that? PLease help me.

THanks a lot
Vanaja


Posted by Derin Fette on January 25, 2001 7:59 PM

Use data validation....its pretty easy

Posted by Vanaja on January 26, 2001 8:42 AM

How do I do it through VBA code??

Posted by Deryn on January 26, 2001 10:16 AM


Here it is..

Sub Number255()

With Selection.Validation
.Delete
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="1", Formula2:="255"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub


Sub Text255()
With Selection.Validation
.Delete
.Add Type:=xlValidateTextLength, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="1", Formula2:="255"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

Posted by Deryn on January 26, 2001 10:23 AM

For Text :


Sub Text255()

Range("A1").Select ' u can change this cell
With Selection.Validation
.Delete
.Add Type:=xlValidateTextLength, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="1", Formula2:="255"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub


For Number :

Sub Number255()

Range("A1").Select ' u can change this cell
With Selection.Validation
.Delete
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="1", Formula2:="255"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

HTH

Posted by Vanaja on January 26, 2001 12:55 PM

Thanks a million Deryn

Range("A1").Select ' u can change this cell

For Number :

Sub Number255() Range("A1").Select ' u can change this cell HTH