Excel VBA and error message

Question

Hi All,

I am using the undernoted code as a simple log to record any changes that are made to a workbook however, when the user double-clicks to autofill a range of cells they are getting a Run-time error '13'; type mismatch. Can anyone advise me of any changes I would need to make to my code to avoid this?

Thanks in advance.

[HTML][/Dim PreviousValue

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Value <> PreviousValue Then

Sheets("log").Cells(65000, 1).End(xlUp).Offset(1, 0).Value = _

Application.UserName & " changed cell " & " " & ActiveSheet.Name & " " & Target.Address _

& " from " & PreviousValue & " to " & Target.Value & " at: " & Format(Time, "hh:mm:ss") & " on: " & Format(Date, "dd/mm/yy")

End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

PreviousValue = Target.Value

End SubHTML]

Answer 1

It is very simple:

If more then one cell is changed Target.Value returns an array instead of just a value, hence the RTE.

You can exclude this situation with

  if Target.Count > 1 then exit sub

in both subs.

But at the end it means your code did not work.

Therefore Excel has a build in Track Changes feature, see Review\Changes.

Andreas.

Answer 2

Hi Andreas,

Indeed, large selections will lead to performance issues.

Events driven codes should always be limited to a specific range, using the Intersect method, or limited to the UsedRange.

Option Explicit

Dim PrevValue As Object, Cell As Range

Const Rng As String = "A1:Z100"

Private Sub Worksheet_Change(ByVal Target As Range)

If PrevValue is Nothing then exit sub

If Not Intersect(Target, Me.Range(Rng)) Is Nothing Then

    For Each Cell In Intersect(Target, Me.Range(Rng))

        If PrevValue(Cell.Address) <> Cell.Value Then

            Sheets("log").Cells(65000, 1).End(xlUp).Offset(1, 0).Value = _

            Application.UserName & " changed cell " & " " & Me.Name & " " & Cell.Address _

            & " from " & PrevValue(Cell.Address) & " to " & Cell.Value & " at: " & Format(Time, "hh:mm:ss") & " on: " & Format(Date, "dd/mm/yy")

        End If

    Next

End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Me.Range(Rng)) Is Nothing Then

    Set PrevValue = CreateObject("Scripting.Dictionary")

    For Each Cell In Intersect(Target, Me.Range(Rng))

        PrevValue.Add Key:=Cell.Address, Item:=Cell.Value

    Next Cell

End If

Thank you for your observations :)

Each method has advantages and disadvantages, as you already know, there are many ways to solve a problem, but a user should decide what is best for him, in his scenario. For example, if user choose to exit when Target.Cells.Count>1, this will prevent him from capturing mass changes like Ctrl+Enter in a selected range, or Ctrl+V over a range of cells. So Alan has to know all implications of not allowing the code to run when Target.Cells.Count>1 and he wants just to avoid the error and ignore mass changes.

Personally, I consider that if he needs to track the changes, then the code must run, no matter if the change is for 1 cell or multiple cells, otherwise the log will be incomplete.

Answer 3

This code will work in any cases, even if the target has only one cell or multiple cells.

[cough cough]

Try and select one (or more) whole columns and/or rows... several times...

Even if you modify you're code to skip unused cells, you'll see that you run into trouble after a short time.

Andreas.

Answer 4

Hi Alan,

When multiple cells are selected or changed in the same time, the Target range from Worksheet_SelectionChange(ByVal Target As Range) or Worksheet_Change(ByVal Target As Range) will contain multiple cells. Your code will have to deal with this case too, you can try creating a collection or a dictionary object to store data and compare the values.

Here is an example:

<code>

Option Explicit

Dim PrevValue As Object, Cell As Range

Private Sub Worksheet_Change(ByVal Target As Range)

For Each Cell In Target

    If PrevValue(Cell.Address) <> Cell.Value Then

        Sheets("log").Cells(65000, 1).End(xlUp).Offset(1, 0).Value = _

        Application.UserName & " changed cell " & " " & Me.Name & " " & Cell.Address _

        & " from " & PrevValue(Cell.Address) & " to " & Cell.Value & " at: " & Format(Time, "hh:mm:ss") & " on: " & Format(Date, "dd/mm/yy")

    End If

Next

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Set PrevValue = CreateObject("Scripting.Dictionary")

For Each Cell In Target

    PrevValue.Add Key:=Cell.Address, Item:=Cell.Value

Next Cell

End Sub

</code>

This code will work in any cases, even if the target has only one cell or multiple cells.

Answer 5

Thanks Andreas that's a perfect solution.