VBA Code causing Excel File to "Open and Repair"

[jsticca]

I have the following VBA code to compile dynamic ranges in a certain tab into single data validation lists in other tabs.

The macro works when I open the Excel file but I'm forced to do an "Open and Repair" each time, is there something I can change in the code to fix this?

Please see below:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Lrow As Single
    Dim Lrow2 As Single
    Dim a, el As Range
    Dim b, el2 As Range
    Dim rng1 As Range, rng2 As Range, rng3 As Range, rng4 As Range, rng5 As Range, rng6 As Range
    
        Set rng1 = Range("iPod_inventory")
        Set rng2 = Range("iPhone_inventory")
        Set rng3 = Range("iPad_inventory")
        
        Set rng4 = Range("iPod_scope")
        Set rng5 = Range("iPhone_scope")
        Set rng6 = Range("iPad_scope")
        
        Lrow = Sheets("iDiR Inventory").Range("G" & Rows.Count).End(xlUp).Row
        Lrow2 = Sheets("iDiR Repairs").Range("F" & Rows.Count).End(xlUp).Row
        
    For Each el In rng1
        a = a & el.Value & ","
    Next
    
    For Each el In rng2
        a = a & el.Value & ","
    Next


    For Each el In rng3
        a = a & el.Value & ","
    Next
    
        For Each el2 In rng4
        b = b & el2.Value & ","
    Next
    
    For Each el2 In rng5
        b = b & el2.Value & ","
    Next


    For Each el2 In rng6
        b = b & el2.Value & ","
    Next
    
    With Sheets("iDiR Inventory").Range("H3:H" & Lrow).Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:=a
    End With
        
    With Sheets("iDiR Inventory").Range("G3:G" & Lrow).Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:="=Suppliers"
    End With
    
    With Sheets("iDiR Inventory").Range("L3:L" & Lrow).Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:="=Quality"
    End With
    
    With Sheets("iDiR Repairs").Range("F3:F" & Lrow2).Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:=b
    End With
    
        Set rng1 = Nothing
        Set rng2 = Nothing
        Set rng3 = Nothing
        Set rng4 = Nothing
        Set rng5 = Nothing
        Set rng6 = Nothing
        
End Sub

Thanks!