Solved: Error 13

[Gert Jan]

Hi,
Over here car-registratationnumbers are written in the format 01-VB-AX.
for correct input i use this code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim strKenteken As String
If Not Intersect(Target, Range("B2:B1000")) Is Nothing Then
If Len(Target) = 6 Then
strKenteken = Target.Text
Target = UCase(Left(strKenteken, 2)) & "-" & _
UCase(Mid(strKenteken, 3, 2)) & "-" & UCase(Right(strKenteken, 2))
End If
End If
End Sub

This works fine when i type in the registrations but, when i copy more then one value into column B, or delete more then one cell at a time i get error 13 , "type does not match"(my Excel is speaking Dutch, so this translation might be a bit off).
I changed the code into a code that acts after clicking a command button, that does the job.
But, automation is all about not clicking buttons is'nt it? :laugh2:
My question; can this code be chanched so that it keeps workin' automatic, or am i condemned to clicking buttons on this one

Gert Jan

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Dim strKenteken As String
If Not Intersect(Target, Range("B2:B1000")) Is Nothing Then
For Each cell In Target
If Len(cell) = 6 Then
strKenteken = cell.Text
cell = UCase(Left(strKenteken, 2)) & "-" & _
UCase(Mid(strKenteken, 3, 2)) & "-" & UCase(Right(strKenteken, 2))
End If
Next cell
End If
End Sub

[Gert Jan]

As a man spoke in july 1969;
That's one small step for XLD, one giant leap for Gert Jan.

Tanks for helping XLD :bow:

Gert Jan