VBA: How to handle runtime error 13 with multiple inputs caused by auto-completion

Asked Oct 17 '19 at 11:46

Active Oct 17 '19 at 11:46

Viewed 27 times

I try to implement a feature that automatically creates a comment attached to a cell, if a certain letter was typed into this cell by a user. The switch-case statement works fine as long as I don't use autocompletion, e.g. I type in an "A" and want to fill it into the next cells by dragging the green frame towards those cells. Then I immediately receive a runtime error 13: mismatched types.

To counter this I tried to add a foreach-loop.


Private Sub Worksheet_Change(ByVal Target As Range)

Dim cell As Variant
 For Each cell In Target
  Application.EnableEvents = False

   Select Case Target.Cells

    Case Is = "A": Target.AddComment ("text: ")
    Case Is = "B": Target.AddComment ("text: ")
    Case Is = "C": Target.AddComment ("text: ")
    Case Is = "D": Target.AddComment ("text: ")
    Case Is = "E": Target.AddComment ("text: ")
    Case Is = "F": Target.AddComment ("text : ")

   End Select
  Application.EnableEvents = True
 Next cell

On Error Resume Next

End Sub

However, the error still appears.

asked Oct 17 '19 at 11:46

greenfield

3

Try changing this `Select Case Target.Cells` to `Select case cell.value`. This will take each cell and check its value individually, not the entire range at once. – Plutian Oct 17 '19 at 11:52
2

Also, define `Cell` as `Range`. – Foxfire And Burns And Burns Oct 17 '19 at 12:00
Thank you! After changing the code I now receive a runtime error 5 – greenfield Oct 17 '19 at 12:09
Which line errors? The code works for me. – SJR Oct 17 '19 at 12:26
Have you also changed `Target.AddComment ("text: ")` etc to `cell...`? – SJR Oct 17 '19 at 12:34
it's solved! I am still new to VBA so I was not seeing the obvious... Thanks a lot to all of you! – greenfield Oct 17 '19 at 12:43

VBA: How to handle runtime error 13 with multiple inputs caused by auto-completion

0 Answers0