Format Cell Value to Ten Characters

[Gos-C]

Hi all,

I have the following code which formats the cell values in column A to 10 characters on entry:

Sub FormatToTenCharacters()
   Dim Cell As Range, LR As Long
   LR = Range("A" & Rows.Count).End(xlUp).Row
   For Each Cell In Range("A1:A" & LR)
        If Len(Cell.Value) < 10 Then Cell.Value = "'" & Application.Rept("0", 10 - Len(Cell.Value)) & UCase(Cell.Value)
    Next Cell
End Sub

For example:

Enter excel in A1 and it changes to '00000EXCEL
Enter 123456 in A2 and it changes to '0000123456
Enter abc456 in A3 and it changes to '0000ABC456

I want to format it as text so that it does not have the apostrophe ('). Any help?

Thank you,
Gos-C

 

[Gos-C]

It works after I also added the UserInterfaceOnly line.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Target.Column <> 11 Then Exit Sub 'only works in Columns K
        If Target.Count > 1 Then Exit Sub
            If Target <> "" Then
    [COLOR="Blue"]ActiveSheet.Protect Password:="test", UserInterfaceOnly:=True[/COLOR]
    Target.NumberFormat = "@"
        If Len(Target.Value) <= 10 Then Target.Value = Application.Rept("0", 10 - Len(Target.Value)) & UCase(Target.Value)
    End If
Application.EnableEvents = True
End Sub

Thank you very much for your help, mikerickson.

Gos-C