O código esta limitado apenas a celula ativa
Cria um range para toda coluna Z

exemplo Range("Z:Z")

GFSouza, muito obrigado pela resposta !
Mas eu não consegui fazer a edição que sugeriu, estou com esse problema para varias colunas em minha planilha, e todas possuem uma quantidade de caracteres maiores que a permitida, com a ferramenta de validação de dados não estou conseguindo atribuir o que desejo

Posta a planilha com o exemplo que deseja
Uma pergunta: qual a ação para as colunas com mais de 9 caracteres?

Tente isso:

Sub LimitarCaractere()
Dim lin As Long
ul = ActiveSheet.Range("Z" & Rows.Count).End(xlUp).Row
For i = 1 To ul
If Len(ActiveSheet.Range("Z" & i).Value) > 9 Then
ActiveSheet.Range("Z" & i).Value = Left(ActiveSheet.Range("Z" & i).Value, 10)
End If
Next i
End Sub

Abrç!

Se eu entendi basta colocar a macro no evento change da planilha

Boa noite.
Mais uma contribuição.