Error 13 in VBA Script when inserting/deleting a line

[stephfr]

Hi

When I attempt to insert or delete a line from my worksheet, I get an Error 13 (Incompatible type I think would be the translation to english).

This is an extract from the routine (which works perfectly well when I don't need to insert a line LOL):

Private Sub Worksheet_Change(ByVal Target As Range)

Dim KeyCellCaps As Range
Dim KeyCellNom As Range
Dim KeyCellMedia As Range
Dim KeyCellPrenom As Range
Dim KeyCellVille As Range
Dim KeyCellPostale As Range
Dim KeyCellTel As Range
Dim KeyCellFax As Range

Set KeyCellCaps = Range("A:A")
Set KeyCellNom = Range("A:A")
Set KeyCellPrenom = Range("B:B")
Set KeyCellMedia = Range("C:C")
Set KeyCellPostale = Range("E:E")
Set KeyCellVille = Range("F:F")
Set KeyCellTel = Range("G:G")
Set KeyCellFax = Range("H:H")

Column = "Q"


' Mettre le nom en majuscule et rajouter la date d'ajout

If Not Application.Intersect(KeyCellCaps, Range(Target.Address)) _
Is Nothing Then

Target.Value = UCase(Target.Text)
Intcolumn = Target.Column
introw = Target.Row
Cells(introw, Column) = Now()

End If

End Sub

When I go to debug, it is stopped on Target.Value = UCase(Target.Text)

Thanks
Steph

 

[Jonmo1]

When you're inserting a row, then the ENTIRE row has changed.
So Target is a range from Column A to Column IV (or whatever is last column in your version of xl)

This line
Target.Value = UCase(Target.Text)
Only works if Target is a SINGLE cell.

Try adding a line at the top

If Target.Count > 1 Then Exit Sub


Also, a side note
If Not Application.Intersect(KeyCellCaps,Range(Target.Address))
The bolded part is redundant (nothing wrong with it, but it's not necessary)
It's like writing Range(Range("A1").Address)
When all you need is Range("A1")

You should just write Target

If Not Application.Intersect(KeyCellCaps, Target)

 

[stephfr]

I have no brain... I had that in before I changed the code and then didn't notice when I redid it

Thank you very much it works perfectly now!!

Steph

 

[Jonmo1]

Glad to help, thanks for the feedback.