Excel vba error while capturing old value

Question

I am getting an error when I am trying to capture an old value from a cell: run-time error '13' Type mismatch.

This is the code I am using:

Dim oldValue As Variant

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

oldValue = Target(1, 1).Value
MsgBox oldValue

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

'check if one of the target columns is changed
If Target.Cells.Column = 6 Or Target.Cells.Column = 9 Or Target.Cells.Column = 10 Or Target.Cells.Column = 11 Then

'Set variables
    Dim LogActivity As String
    Dim cRow As Integer
    Dim pRowCount As Integer

    Dim wsPBS As Worksheet
    Dim wsHistoric As Worksheet

    Set wsPBS = Sheets("PBS")
    Set wsHistoric = Sheets("Historic")
    cRow = Target.Cells.Row
    pRowCount = wsHistoric.Range("A" & Rows.Count).End(xlUp).Row + 1

'Check for blanks on PBS sheet and exit if entry is not complete
    Dim BlankCount As Integer
    BlankCount = 0

    If wsPBS.Range("D" & cRow).Value = "" Then BlankCount = BlankCount + 1
    If wsPBS.Range("E" & cRow).Value = "" Then BlankCount = BlankCount + 1
    If wsPBS.Range("F" & cRow).Value = "" Then BlankCount = BlankCount + 1
    If wsPBS.Range("H" & cRow).Value = "" Then BlankCount = BlankCount + 1
    If wsPBS.Range("I" & cRow).Value = "" Then BlankCount = BlankCount + 1
    If wsPBS.Range("J" & cRow).Value = "" Then BlankCount = BlankCount + 1

    If BlankCount >= 1 Then Exit Sub

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

        If Target.Cells.Column = 6 Then LogActivity = "Owner change"
        If Target.Cells.Column = 9 Then LogActivity = "Status change"
        If Target.Cells.Column = 10 Then LogActivity = "Priority change"
        If Target.Cells.Column = 11 Then LogActivity = "Completion rate"

    Range("C" & cRow & ":O" & cRow).Select
        Selection.Copy

    wsHistoric.Select
    wsHistoric.Range("F" & pRowCount).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False

    wsHistoric.Range("A" & pRowCount).Value = Date
    wsHistoric.Range("B" & pRowCount).Value = Time
    wsHistoric.Range("C" & pRowCount).Value = Application.UserName
    wsHistoric.Range("D" & pRowCount).Value = LogActivity
    wsHistoric.Range("E" & pRowCount).Value = oldValue

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End If

End Sub

The value is supposed to be stored in a global dim called 'oldValue' so I can use it later on in my code. The cell I am clicking does contain a string.

Any suggestions?

What is `Target.Value` in this case? Also it's not robust to assume that you'll only ever select a string, I'd declare that `As Variant` and then test its type. — BigBen, Jun 19 '19 at 18:49
are you changing more than one cell at a time? If so, `Target` will be a range containing more than one cell, and you won't be able to get a `value` from it — Greg Viers, Jun 19 '19 at 18:50
@GregViers well you will be able to get a `value`, but it will *not* be a string, but an array. — BigBen, Jun 19 '19 at 18:51
No, it's a single cell. I changed the string to a variant and added a Msgbox and it shows the correct, old, value. But when in the rest of the code I go to paste it, it changes to a different value. When I select another value in the drop-down, the run-time error pops back up — Chis.Wisselo, Jun 19 '19 at 18:55
Maybe share the rest of your code, what are you actually doing with `oldValue`? — BigBen, Jun 19 '19 at 18:59
I updated the question with the entire code, maybe I'm overlooking something silly here. — Chis.Wisselo, Jun 19 '19 at 19:01
You're `Select`ing within the `Worksheet_Change` event. That fires the selection change event again. You need to disable events within the worksheet_change. Or even better, don't `Select`. — BigBen, Jun 19 '19 at 19:05
Makes sense... what was that syntax again? Application.EnableEvents = False? — Chis.Wisselo, Jun 19 '19 at 19:06
`Application.EnableEvents = False`, and then `= True` at the end. But you don't need to `Select` at all: `Range("C" & cRow & ":O" & cRow).Copy`. — BigBen, Jun 19 '19 at 19:07
Thank you BigBen. I tried that and it's working like a charm now... Thank you very much! — Chis.Wisselo, Jun 19 '19 at 19:10

BigBen · Answer 1 · 2019-06-19T21:12:03.200

The main issue:

You're Selecting within the Worksheet_Change event.

Range("C" & cRow & ":O" & cRow).Select
Selection.Copy

That fires the Selection_Change event again, overwriting oldValue.

No need to Select here. See How to avoid using Select in Excel VBA.

Range("C" & cRow & ":O" & cRow).Copy

The secondary (yet still very important issue):

In your original version of the selection change:

Dim oldValue As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    oldValue = Target.Value
End Sub

This will throw a type mismatch error if Target doesn't contain a String or something that can be coerced to a String.

In your instance, that was because Target actually was multiple cells: Range("C" & cRow & ":O" & cRow). But your code would also throw an error if you selected a cell with an error value (#N/A, #DIV/0, etc.).

The fix:

First of all, avoid using Select, as already noted.

If for some (rare) reason you absolutely need to Select, then toggle events off and on:

Private Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    ... do your stuff
    Application.EnableEvents = True
End Sub

Lastly, within the selection change, instead of assuming that you'll only select a string, or only select one cell, add some validation.

Dim oldValue As Variant

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge <> 1 Then Exit Sub ' ignore a multi-cell selection
    If IsError(Target.Value) Then Exit Sub ' ignore selection of errors

    oldValue = Target.Value
End Sub

score 0 · Answer 2 · answered Jun 19 '19 at 18:56

0

Try identifying a single cell withing Target:

oldValue = Target(1,1).Value

answered Jun 19 '19 at 18:56

Greg Viers

3,473
3
18
36

1

This will still throw a type mismatch error if `Target` does not contain a string or something that can be coerced to a string. E.g. if the cell contains an error. – BigBen Jun 19 '19 at 18:57
That gives me the 1st row/ column value in the paste... However clicking the cell still does show the old value. So not sure why it's not saving correctly. – Chis.Wisselo Jun 19 '19 at 18:58
I updated the question with the entire code, maybe I'm overlooking something silly here. – Chis.Wisselo Jun 19 '19 at 19:01

Excel vba error while capturing old value

2 Answers2