Option Explicit

Sub OpenFolderAndListFiles()
    Dim folderPath As String
    Dim fDialog As FileDialog
    Dim fso As Object
    Dim folder As Object
    Dim rowIndex As Integer
    
    ' Create a FileDialog object
    Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
    
    ' Show the folder dialog
    If fDialog.Show = -1 Then
        folderPath = fDialog.SelectedItems(1)
        
        ' Create a modification log sheet if not exists
        On Error Resume Next
        ThisWorkbook.Worksheets("modificari").Delete
        On Error GoTo 0
        ThisWorkbook.Worksheets.Add(After:=Sheets(Sheets.Count)).Name = "modificari"
        
        ' List the chosen folder files and subfolder files
        rowIndex = 4
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set folder = fso.GetFolder(folderPath)
        ListFilesInFolder folder, rowIndex, 0
        
        ' Update hidden sheet with current file details
        UpdateHiddenSheet folder
    Else
        MsgBox "No folder selected."
    End If
End Sub

Sub ListFilesInFolder(ByVal folder As Object, ByRef rowIndex As Integer, ByVal level As Integer)
    Dim subfolder As Object
    Dim file As Object
    Dim indent As String
    
    ' Create indent string based on the level of hierarchy
    indent = String(level * 3, " ")
    
    ' List the folder name with indent
    With Sheets("Sheet1").Cells(rowIndex, 3)
        .Value = indent & folder.Name
        .Font.Size = 14
        .Font.Color = RGB(0, 0, 0)  ' Black color
        .Font.Bold = True
    End With
    rowIndex = rowIndex + 1
    
    ' Loop through all files in the folder
    For Each file In folder.Files
        With Sheets("Sheet1").Cells(rowIndex, 3)
            .Hyperlinks.Add Anchor:=Sheets("Sheet1").Cells(rowIndex, 3), Address:=file.path, TextToDisplay:=indent & "   " & file.Name
        End With
        Sheets("Sheet1").Cells(rowIndex, 4).Value = file.DateLastModified
        rowIndex = rowIndex + 1
    Next file
    
    ' List subfolders and their contents recursively
    For Each subfolder In folder.Subfolders
        ListFilesInFolder subfolder, rowIndex, level + 1
    Next subfolder
End Sub

Sub UpdateHiddenSheet(ByVal folder As Object)
    Dim hiddenSheet As Worksheet
    On Error Resume Next
    Set hiddenSheet = Sheets("HiddenSheet")
    On Error GoTo 0
    
    If hiddenSheet Is Nothing Then
        Set hiddenSheet = Sheets.Add(After:=Sheets(Sheets.Count))
        hiddenSheet.Name = "HiddenSheet"
        hiddenSheet.Visible = xlSheetVeryHidden
    End If
    
    hiddenSheet.Cells.Clear
    SaveFolderDetails hiddenSheet, folder, 1
End Sub

Sub SaveFolderDetails(ByRef ws As Worksheet, ByVal folder As Object, ByRef rowIndex As Integer)
    Dim subfolder As Object
    Dim file As Object
    
    ' Save folder path
    ws.Cells(rowIndex, 1).Value = folder.path
    rowIndex = rowIndex + 1
    
    ' Save file details
    For Each file In folder.Files
        ws.Cells(rowIndex, 1).Value = file.path
        ws.Cells(rowIndex, 2).Value = file.DateLastModified
        rowIndex = rowIndex + 1
    Next file
    
    ' Save subfolder details recursively
    For Each subfolder In folder.Subfolders
        SaveFolderDetails ws, subfolder, rowIndex
    Next subfolder
End Sub

Sub RefreshData()
    Dim fso As Object
    Dim hiddenSheet As Worksheet
    Dim folder As Object
    Dim rowIndex As Integer
    
    On Error Resume Next
    Set hiddenSheet = Sheets("HiddenSheet")
    On Error GoTo 0
    
    If hiddenSheet Is Nothing Then
        MsgBox "No data to refresh."
        Exit Sub
    End If
    
    ' Load and compare hidden sheet data
    Set fso = CreateObject("Scripting.FileSystemObject")
    rowIndex = 4
    Set folder = fso.GetFolder(hiddenSheet.Cells(1, 1).Value)
    
    Dim currentData As Collection
    Set currentData = LoadCurrentData(hiddenSheet)
    
    ' Clear existing hyperlinks in Sheet1
    Sheets("Sheet1").Hyperlinks.Delete

    ' Clear existing contents in Sheet1 except first two columns
    Sheets("Sheet1").Columns("C:D").ClearContents
    
    Dim updatedData As Collection
    Set updatedData = New Collection
    
    ' Collect updated data
    CollectFolderData updatedData, folder
    
    ' Compare data and log changes
    CompareData currentData, updatedData
    
    ' List files again with updated data
    ListFilesInFolder folder, rowIndex, 0
    
    ' Update hidden sheet with new data
    UpdateHiddenSheet folder
End Sub

Function LoadCurrentData(ws As Worksheet) As Collection
    Dim data As Collection
    Dim i As Long
    
    Set data = New Collection
    
    Dim path As String
    Dim lastModified As Date
    
    For i = 1 To ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
        path = ws.Cells(i, 1).Value
        lastModified = ws.Cells(i, 2).Value
        data.Add Array(path, lastModified)
    Next i
    
    Set LoadCurrentData = data
End Function

Sub CollectFolderData(ByRef data As Collection, ByVal folder As Object)
    Dim subfolder As Object
    Dim file As Object
    
    ' Add file details to collection
    For Each file In folder.Files
        data.Add Array(file.path, file.DateLastModified)
    Next file
    
    ' Add subfolder details recursively
    For Each subfolder In folder.Subfolders
        CollectFolderData data, subfolder
    Next subfolder
End Sub

Sub CompareData(currentData As Collection, updatedData As Collection)
    Dim wsModificari As Worksheet
    Set wsModificari = Sheets("modificari")
    
    Dim i As Long, j As Long
    Dim currentFile As Variant
    Dim updatedFile As Variant
    Dim changesRow As Long
    changesRow = wsModificari.Cells(wsModificari.Rows.Count, 1).End(xlUp).Row + 1

    Dim found As Boolean
    
    ' Check for new or modified files
    For i = 1 To updatedData.Count
        updatedFile = updatedData(i)
        found = False
        
        For j = 1 To currentData.Count
            currentFile = currentData(j)
            
            If currentFile(0) = updatedFile(0) Then
                found = True
                If currentFile(1) <> updatedFile(1) Then
                    ' File modified
                    wsModificari.Cells(changesRow, 1).Value = updatedFile(0)
                    wsModificari.Cells(changesRow, 2).Value = "Modified"
                    wsModificari.Cells(changesRow, 3).Value = Now
                    changesRow = changesRow + 1
                End If
                Exit For
            End If
        Next j
        
        If Not found Then
            ' New file
            wsModificari.Cells(changesRow, 1).Value = updatedFile(0)
            wsModificari.Cells(changesRow, 2).Value = "Added"
            wsModificari.Cells(changesRow, 3).Value = Now
            changesRow = changesRow + 1
        End If
    Next i
    
    ' Check for deleted files
    For i = 1 To currentData.Count
        currentFile = currentData(i)
        found = False
        
        For j = 1 To updatedData.Count
            updatedFile = updatedData(j)
            
            If currentFile(0) = updatedFile(0) Then
                found = True
                Exit For
            End If
        Next j
        
        If Not found Then
            ' File deleted
            wsModificari.Cells(changesRow, 1).Value = currentFile(0)
            wsModificari.Cells(changesRow, 2).Value = "Deleted"
            wsModificari.Cells(changesRow, 3).Value = Now
            changesRow = changesRow + 1
        End If
    Next i
End Sub

Private Sub Workbook_Open()
    ' Ensure data is kept between sessions
    Dim hiddenSheet As Worksheet
    On Error Resume Next
    Set hiddenSheet = Sheets("HiddenSheet")
    On Error GoTo 0
    
    If Not hiddenSheet Is Nothing Then
        Call RefreshData
    End If
End Sub

this code because on refresh delete files from  hidden sheet list all files like deleted on sheet modificari ,.