Restricting cell input

Maarten.
Try formatting the cells. Select all cells then format, then "text".
Then select the same cells and hit "Data" then "Validation". Then select the criteria, i.e. "any value" then "text length". This should give you the result you're looking for. This works for Excel97, but you didn't specify. In Excel97 you can also specify an error alert. Play with it a bit.
BLean30939@aol.com

Thanks for that Bill, I had found it, but wat I wanted it to do is stop you typing further.
This option will tell you what you have done wrong but if the input restriction is lets say 50 caracters it means you have to count them all.
Isn't there a way that excel stops the entry of more caracters?
By the by I work on excel 7.0, but the program is for 2000 and I also have access to 97 so it doesn't matter which excel formats you use.

Maarten
You can format the column as "wrap text" and size the column width so that the maximum number of characters fits exactly.
This does not restrict the typing, but you can see on the screen as soon as the maximumm no. of characters is exceeded.
Celia

You could try the following;
which will limit the user input into a sheets
cell to to length you set ???
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TempTxt
Dim MaxCharc As Integer

MaxCharc = 3 ' set your max here

If Len(Target.Text) > MaxCharc Then
MsgBox "Can only enter " & MaxCharc & " characters"
TempTxt = Left(Target.Text, MaxCharc)
Application.EnableEvents = False
Target.Activate
ActiveCell = TempTxt
Application.EnableEvents = True
End If
End Sub

Ivan

Thanks Ivan it looks impressive but I am afraid that is way over my head.
Where do you put the sheet name and cell number?
The input cell is "G57" on sheet "Input sheet"
and the target cell is "AA57" on "Daily report"
Thanks for your help.

Maarten
1) In VBA editor (press Alt F11 to get to this)
2) Press Ctrl R to get to project explorer
3) Doulble click on the "Input Sheet"
4) In the left pane drop list select Worksheet
5) In the right pande dropdown list select "Change" OR
Just copy & paste this routine in

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TempTxt
Dim MaxCharc As Integer

MaxCharc = 3 ' set your max here
If Target.Address = "$G$57" And Len(Target.Text) > MaxCharc Then

'If Len(Target.Text) > MaxCharc Then
MsgBox "Can only enter " & MaxCharc & "characters"
TempTxt = Left(Target.Text, MaxCharc)
Application.EnableEvents = False
Target.Activate
ActiveCell = TempTxt
Application.EnableEvents = True
End If
End Sub

Ivan

Ok ok. I see a lot of questions on this board that would be solved if people just learned how to use their "control" toolbox.

With a control toolbox, what you can do is have a user entry field.
Just to be simpler, you can have the contents of this user field linked to a cell, so
whatever the user enters in this field, is automatically put in a cell.
And in the user field, you can specify the maximum length of the entry.

How to make it:
Right click on a toolbar, it will give you a list of available toolbars,
select to have the the ControlToolbar visible.
Once the controltoolbar is visible, click on the button that has "abc" on it (and is called
textbox" if you hold your mouse over it. You will then maker your user field for entering
text.
Now comes the hard part. If when you move your mouse over this field, your
mouse cursor changes shape into the normal cursor you see when typing, you are in text entry mode.
If instead, your mouse changes in the resize cursor then you are in the "control form edit mode"
which is where you want to be. To switch between one and the other, click on the icon with a triangle
and a pencil at the top of the ControlToolbar.
Ok, so when you are in the "control form edit mode", and your mouse is over this user field,
right click and select properties. You'll get a long list. Look through this list.
You'll find one called LinkedCell. There you can enter what cell you want the user field contents to appear.
You have to enter this cell in the fillowing format : the letter R then the row number the letter C then the
column number, e.g. R1C5. Next, also in this list of properties, look for MaxLength. In that field you can select
the maximum number of characters you want your users to be able to enter.

I have put a sample use of this in an excel file here:
http://www.mnsi.net/~fciuca/limitedentry.xls
I hope you can acess it. When you click on the link it should ask you to save to disk.
(it may warn you that there is a macro - because I guess, a user field is a form of macro.

If you figure this out, you will have lots of fun playing with the rest of the stuff in the control
toolbar. I have combined that with a lot of macros to make excel files that look almost like stand alone
applications.