0

I'm working on a Excel document with a list of items. Because many people are working with this document I need to register by whom, when and where something is changed. All that is realized with VBA in Excel.

When someone change a value in a cell the author name is written in a column, the date and time is written in a column and the cell that has changed gets a thick red border. After checking the change by 2 persons; the datum, time and red border will be removed.

Till here everything is ok and working but when someone wants to insert a new row, delete a complete row or paste a complete row, via the right mouse click functionality there occurs an error.

"Error 13: types do not match".

I don't understand what's going wrong.

Below the Excel VBA code

Br. CLE

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
'-----------------------------
' Set selection rulers
'-----------------------------

    [GesRij] = Target.Row
    [GesKol] = Target.Column
    
'-----------------------------
' Calculate LastRow & LastColumn
'-----------------------------

    Dim LastRow As Long
    Dim LastColumn As Long
    Dim StartCell As Range
    Dim Check_01, Check_02
                
    aw_name = ActiveWorkbook.Name
        
    Set sh_name = Worksheets("Blad1")
    Set StartCell = Range("A2")
        
        'Find Last Row and Column
          LastRow = sh_name.Cells(sh_name.Rows.Count, StartCell.Column).End(xlUp).Row
          LastColumn = sh_name.Cells(StartCell.Row, sh_name.Columns.Count).End(xlToLeft).Column

'-----------------------------
'Clear changes
'-----------------------------

    sh_name = "Blad1"
                         
    With ActiveWorkbook.Sheets(sh_name)
    
          For i = 2 To LastRow

            Check_01 = Workbooks(aw_name).Sheets(sh_name).Cells(i, 8).Value
            Check_02 = Workbooks(aw_name).Sheets(sh_name).Cells(i, 9).Value
                               
            If (Check_01 = "1") And (Check_02 = "1") Then
                'Clear data
                .Range(Cells(i, 1), Cells(i, 2)).ClearContents
                
                'Clear marker
                .Range(Cells(i, 3), Cells(i, 6)).Borders.Color = vbBlack
                .Range(Cells(i, 3), Cells(i, 6)).Borders.LineStyle = xlContinuous
                .Range(Cells(i, 3), Cells(i, 6)).Borders.Weight = xlThin
                
                Workbooks(aw_name).Sheets(sh_name).Cells(i, 8).Value = ""
                Workbooks(aw_name).Sheets(sh_name).Cells(i, 9).Value = ""
            End If
        Next i
    End With
    
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

'-----------------------------
' Registrate date, time and author of the change
'-----------------------------

    Dim Bereik As Range, Controle As Range
    Dim DataOld, DataNew
 
    Dim aw_name
    Dim sh_name
    
    Set Bereik = ActiveWorkbook.Sheets("Blad1").Range("C:F")
    Set Controle = Intersect(Target, Bereik)
    
    With ActiveWorkbook.Sheets(1)
                              
        If Not Controle Is Nothing Then
            'Write data
              .Range("A" & Target.Row) = DateValue(Now)
              .Range("B" & Target.Row) = TimeValue(Now)
              .Range("G" & Target.Row) = ThisWorkbook.BuiltinDocumentProperties("Last Author")
            
            'Markeer wijziging
              DataNew = Target.Value
    
                If DataOld <> DataNew Then
                    Target.Borders.LineStyle = xlContinuous
                    Target.Borders.Weight = xlThick
                    Target.Borders.Color = vbRed
                End If
    
                If Target.Cells.Count > 1 Then
                    Exit Sub
                End If
            
            ClOud = Target.Value
        End If
    End With
End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
CLE
  • 1
  • Are you getting a specific row highlighted when you get the error? – Chronocidal Oct 18 '20 at 17:33
  • Error occurs in the section: "Registrate date, time and author of the change" Line with code: "If DataOld <> DataNew Then – CLE Oct 18 '20 at 17:35
  • 3
    I've read the code a few times, but I can't see where DataOld is assigned? – DS_London Oct 18 '20 at 17:47
  • @DS_London So, might be a need for [`Option Explicit`](https://stackoverflow.com/questions/2454552/what-do-option-strict-and-option-explicit-do) then? – Chronocidal Oct 18 '20 at 21:42
  • @Chronocidal Maybe so ... but DataOld is declared, just not assigned to anything. – DS_London Oct 19 '20 at 05:42
  • @Chronocidal - I understand what you mean. I replaced (corrected) the line "ClOud = Target.Value" for "DataOld = Target.Value" between the end_if and End_with statement at the end of the VB-script but it doesn't fix the problem. – CLE Oct 19 '20 at 09:05

0 Answers0