type mismatch Run time error 13, while Undoing Ctrl+z for multi deleted cells

0
votes

I'm learning VBA trying in my live Excel sheets.

I have VBA code, 1st got

Runtime Error 13 type mismatch

while deleting multiple cells, with small work I'm not getting the error while deleting multiple cells, but when I do UNDO to the multi deleted cells, the same error occurs.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Column = 6 Then
        ThisRow = Target.Row
        If Target.Cells(1).Value = vbNullString Then Exit Sub
        If Len(Target.Offset(, -4)) = 0 Then
            Range("J" & ThisRow).Value = Time()
            Range("B" & ThisRow).Value = Date
            Range("AA" & ThisRow).Value = Environ("username")
        End If
    End If
End Sub
2
vbaexcel
Is this mean to work for only 1 cell at a time? You could always have a If Target.Count > 1 Then Exit Sub otherwise. - QHarr
Yes GURU,Its working thanks for Assist - Mastan Shaik
This also means it will only respond if you paste a single cell. And you cannot alter more than once cell at once and get the expected result. Is this what you wanted? - QHarr
yes u r right,if i alter again then it giving that altered time and date which i dont want. - Mastan Shaik
Explain more about desired result please? What do you mean by altered time and date? - QHarr

2 Answers

0
votes

Something like this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rng As Range, c As Range

    Set rng = Application.Intersect(Target, Me.Columns(6))
    If rng Is Nothing Then Exit Sub
    For Each c In rng.Cells
        If Len(c.Value) > 0 Then
            If Len(c.Offset(0, -4).Value) = 0 Then
                With c.EntireRow
                    .Cells(1, "J").Value = Time()
                    .Cells(1, "B").Value = Date
                    .Cells(1, "AA").Value = Environ("username")
                End With
            End If
        End If
    Next c
End Sub
0
votes

If only interested in producing a result for single cell changes (i.e. target is a single cell) then you can add:

If Target.Count > 1 Then Exit Sub

Multi-cell changes will then not be responded to and you won't get the type mismatch error of .Value with multiple cells as this property belongs to a single cell range object.

This also means it will only respond if you paste a single cell. And you cannot alter more than once cell at once and get the expected result.

You have a second sheet (Sheet2) which acts as a store of which cells are populated. This is then checked to see if you should respond to the current event if Sheet1.

Option Explicit
Public monitorCell As Range

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim ThisRow
    If Target.Column = 6 Then 
        ThisRow = Target.Row

        If Not IsEmpty(monitorCell) Or Target.Count > 1 Then Exit Sub

        If Len(Target.Offset(, -4)) = 0 Then
            Range("J" & ThisRow) = Time()
            Range("B" & ThisRow) = Date
            Range("AA" & ThisRow) = Environ$("username")
        End If
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Set monitorCell = Worksheets("Sheet2").Range(Target.Address)
End Sub