Embora essa não seja a maneira mais elegante de fazer isso, recomendo que você a teste completamente. Isso parece funcionar. Ajuste isso conforme necessário para a coluna de entrada problemática. Isso é definido para um intervalo agora, não para a coluna inteira, para que as fórmulas funcionem na coluna.
Primeiro, defina o formato da célula para que ele não seja convertido automaticamente em algo para você. Defina o formato da célula para ser o que realmente é digitado usando o evento Selection Change.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set rng2check = Range("A1:A10") 'you can adjust this how you wish
If Not Intersect(ActiveCell, rng2check) Is Nothing Then ActiveCell.NumberFormat = "@"
Set rng2check = Nothing
End Sub
Em seguida, verifique a entrada de dois pontos e substitua-os, converta o valor para Double ou Currency e redefina o formato final da célula para General, ou Currency, o que for melhor para você.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next 'on an error leave events enabled
Application.EnableEvents = False
inputCheck = Target.Text
inputCheck = Replace(inputCheck, ":", ".")
inputCheck = CDbl(inputCheck)
'MsgBox (inputCheck)
Range(Target.Address).NumberFormat = "General"
Range(Target.Address) = inputCheck
Application.EnableEvents = True
End Sub
Alguém pode apontar uma maneira melhor de fazer isso. Alterar o formato da célula para texto primeiro pode não ser a melhor solução, mas consegue o que você deseja. É claro que outra validação de dados pode ser uma boa ideia
Você poderia estender isso para substituir outros caracteres além dos dois pontos, tenho certeza.