Prevent User typing when Limit reached

[wyscavern]

Hello
I am new to the forum, but I am a seasoned user of Microsoft products.
What I am trying to do is use the Data validation (this part is easy) to reach a certain amount of characters in a cell. Then instead of the warning message displaying, I would like a hard stop. So the user would be prevented from typing or copy and pasting more information in the cell then the data validation is allowed.

I can find no thread that actually freezes or locks a cell when the data validation is reached.

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
    If Target <> Cells(1, 1) Then Exit Sub 
     
    If Cells(1, 1).Characters.Count > 55 Then MsgBox "A1 must not exceed 55 characters"
	And cells(1,1) = freeze. 
End Sub

I know that the And cell(cell reference is wrong and there is not a method or function for freeze. But hopefully this helps you understand what I am trying to do.
Thanks

[shg]

You can't do that in a cell; when Excel is in Edit mode, no VBA is running. You could use an ActiveX texbox control and test the length in its Change event.

[wyscavern]

Thank you. I had a feeling that was the case. But I was hoping.

[Cutter]

@ wyscavern

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

[wyscavern]

I was leaving it open...just in case someone else found a way. I have found some other information on Experts exchange that uses a truncate conditional formatting. If this does what I want it to I will post the answer and choose solved.
Thank you.

[wyscavern]

I found some code that will actually truncate the characters in the cell. It does require the spreadsheet to be saved as a Macro enabled workbook.
For current data, you can use this code to truncate:
Open VBA (Alt+F11), point to Insert->Module and copy&paste the following code into this new module and run it by pressing F5.
Please remember to change column reference in code (it is 1 = A column)

Sub TruncateCell()
Dim rng As Range
Dim cll As Range
    Set rng = Sheet1.Columns(1)
    Set rng = Sheet1.Range(rng.Cells(1, 1), rng.Cells(65536, 1).End(xlUp))
    For Each cll In rng.Cells
        cll.Value = Left(cll.Value, 8)
    Next cll
End Sub

Truncating for new values :

Goto VBA, Project Explorer (Ctrl + R), double click on the Thisworkbook object and copy and paste the following code into this module declaration section. Set the condition for your sheet and column (it is Column A - Target.Column = 1 and Sheet1 in code)

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = Sheet1.Name Then
        If Target.Column = 1 Then
            If Len(Target.Value) > 8 Then
                Target.Value = Left(Target.Value, 8)
            End If
        End If
    End If
End Sub

This seemed to work. So even though it does not stop, the user can only see up to 8 of the characters.

[Cutter]

@ wyscavern

Please notice that code tags have been added to your post(s). The forum rules require them so please keep that in mind and add them yourself whenever showing code in any of your future posts. To see instructions for applying them, click on the Forum Rules button at top of the page and read Rule #3.
Thanks.