How to set the "Text Length" in MSexcel 2

[natatbh]

I would like to set a column in a worksheet so that its cells won't except more than 23 characters. I already did this by setting the data validation. The problem is that it only prompts the users after he leaves the cell (at that time he might have already entered 200 characters). I would like to have a way to prompt the user when he enters the 24th character
that he can't enter more than 23 characters. It seems that this can't be done in a straight, simple way. If anyone has some kind of work around please help.

Thanks!
natatbh

 

[carrr]

You can use Data Validation to set the maximum length of text in a cell
With your cell (or cells) selected use Data, Validation... ...choose Text Length from the Allow list and set the min/max length. You can also add an error message you might want displayed.

 

[carrr]

And...after experimenting with this "fix," I realize that--again--your user will enter the text fully before learning the cell limit.
Unless...unless you utilize the Input Message functionality and display a message informing the user of the cell limits. This will pop-up as soon as the user selects the cell for data entry.
This may be the best one can do...as Excel depends on the user to exit the cell/"enter" the data before it can really react to the size of what they've entered.

 

[natatbh]

Thanks carrr,

I understand that Excel doesn't react to the data until the user exits the cell. There is probably no straight way of checking the number of characters before leaving the cell. But I am trying to figure out some kind of work around do do it.

I'd appreciate if you or someone else can help.

Thanks.

 

[xlbo]

Don't know what your requirements are but if you are doing data entry, have you considered using a form for it ???

Within a textbox on a form, you can use the following:

Private Sub TextBox1_Change()
If Len(TextBox1) > 23 Then
MsgBox "You can only enter 23 characters"
TextBox1.Text = Left(TextBox1.Text, 23)
Else
End If
End Sub

which will not allow more than 23 characters to be entered into the textbox Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[natatbh]

Thanks xlbo,

I asked around alot about this topic and it seems that all the answers I got are pointing in the same direction that your'e pointing to. The only problem with this is that the
user can still click on a cell and enter data via the cell. I whould like to restrict the user so that he can enter data "ONLY" via the text box and not via the cell.
I got an idea to open a form with a text box when the user clicks on a cell in the column I want to restrict. Right after the form opens I make a cell in another column the active cell.
After the user finishes typing into the text box I close the form and move the data from the text box to the cell. Now if the user wants to select a cell in the restricted column he has to click on one of its cells again which again opens the form. This all works great but its kind of nervy for the user to have the active cell jump to another column every time he selects a cell.

If you can give me some kind of solution to this stuff you would be a pioneer. I asked around a lot and haven't gotten a solution yet.

Looking For Pioneer,
natatbh

 

[xlbo]

Why don't you unlock all cells except those in the column you want to restrict entries in and then protect the sheet. Unlocked cells can still be changed when protection is on. You can then use the selection of a cell in that column to start the form but it wouldn't matter whether the active cell was in that column or not as the user wouldn't be able to change it anyway. Of course ,you would need to unprotect the sheet before putting the data in and re-protect afterwards but it would be reasonably simple to do Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[HobbitK]

natatbh ...
I had to post a few questions to get this to work. I am VB programmer and doing this for you has shown me just how little I know about VBA, but this works I believe exactly how you desire.
This is what I did ...
Create a UserForm and add it to the Workbook. Place a textbox on the Form.
In worksheet1 add this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Tests active Cell if in "B" calls User form
If ActiveCell.Column = 2 Then
UserForm1.Show vbModal
End If

End Sub

In the textbox on the userform add this code:

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'Called by Selection_Change on Sheet1
'The last 2 lines should be moved to excel closing event.
'Just here for example
Dim strInput As String 'Used to hold User input

If Len(TextBox1) < 4 Then 'Bails out of Sub if Len less than desired length
Exit Sub
Else
KeyAscii = 0 'Deletes 5th character input
MsgBox (&quot;Max 4 characters&quot 'Informs our User
End If
strInput = TextBox1.Text 'Fill our variable
ActiveCell.Value = strInput 'Fill Triggering cell with input
TextBox1 = &quot;&quot; 'Clear for next use
UserForm1.Hide 'Keep working in Excel
Set UserForm1 = Nothing 'Clean up our mess

End Sub

Change the length of the test to whatever you need. You MAY need to set a few properties of the textbox for a string 23 characters long. The only thing to remember is that the LEN function counts spaces, so you MAY need to trim them out before the test.
Sorry this took so long.

HTH
Michael

 

[natatbh]

Thanks hobbitk

xlbo, Regarding your response I whould appreciate if you can give me some advice on how to protect and unprotect a sheet, lock and unlock cells using vba code. I tried using stuff like
'Sheet1.Unprotect (&quot;abcde&quot' and 'ActiveWorkbook.Protect Password:=&quot;abcde&quot;' but I am getting errors. please give me some idea of how to implement this.

Thanks
natatbh