Hello Community,

I worked on a BA report where I had to modify some existing code. So, I made the modifications and tested them on the local file. Once the work was completed, I published the BA report and tried to run it via IFS, but the sup_lignes macro is not executed, and I don’t understand why. I have already made the necessary adjustments to the macro settings in Excel.

Do you have any suggestions to help me, please ? Is there a setting to enable in IFS?

Here’s the “Overdues” code (it’s the first sheet) :

Private Sub Worksheet_BeforedoubleClick(ByVal Target As Range, Cancel As Boolean)


' Exit the procedure if a right-click occurs outside the customer numbers area.
If Intersect(Target, sclients].Columns(1)) Is Nothing Then Exit Sub
' To cancel the display of the context menu on double-click
Cancel = True

Sheets("Data").Activate

eData].AutoFilter Field:=1, Criteria1:=Target.Value

End Sub

Private Sub Worksheet_Deactivate()

Call sup_lignes


End Sub

And here’s the macro ‘sup_lignes’ :

Sub sup_lignes()

    ' Procedure triggered upon exiting the Overdue sheet.
    If Worksheets("Overdues").Cells(2, 1).Value = 1 Or (Data].Rows.Count = 2 Then Exit Sub

    Dim j As Long, prem_ligne As Long, der_ligne As Long
    Dim wsc As Worksheet, wsd As Worksheet

    Set wsc = Worksheets("Créditeurs")
    Set wsd = Worksheets("Débiteurs")

    Application.ScreenUpdating = False

    ' Deletion of creditor lines.
    prem_ligne = bdeb].Cells(1).Row
    der_ligne =     For j = der_ligne To prem_ligne Step -1
        If wsd.Cells(j, 5).Value <= 0 Then wsd.Rows(j).Delete
    Next j

    ' Deletion of debtor lines.
    prem_ligne = bcred].Cells(1).Row
    der_ligne =     For j = der_ligne To prem_ligne Step -1
        If wsc.Cells(j, 5).Value > 0 Then wsc.Rows(j).Delete
    Next j

    ' Marking the Overdues sheet to indicate that the macro has already been run once.
    Worksheets("Overdues").Cells(2, 1).Value = 1

    ' Sorting column E of the "Debtors" sheet starting from row 8.
    Dim lastRow As Long
    lastRow = wsd.Cells(wsd.Rows.Count, "E").End(xlUp).Row ' Find the last non-empty row in column E.

    ' Convert values to numbers if necessary.
    Dim i As Long
    For i = 8 To lastRow
        If IsNumeric(wsd.Cells(i, "E").Value) Then
            wsd.Cells(i, "E").Value = CDbl(wsd.Cells(i, "E").Value)
        End If
    Next i

    ' Sort all columns of the "Debtors" sheet starting from row 8.
    Dim lastCol As Long
    lastCol = wsd.Cells(7, wsd.Columns.Count).End(xlToLeft).Column ' Find the last non-empty column starting from row 8.

    wsd.Range(wsd.Cells(7, 1), wsd.Cells(lastRow, lastCol)).Sort Key1:=wsd.Range("E7"), Order1:=xlAscending, Header:=xlYes ' Sort all columns.

    Application.ScreenUpdating = True

    ' Check and restore the code for `Worksheet_SelectionChange`.
    CheckSelectionChangeCode

End Sub

Sub CheckSelectionChangeCode()
    Dim code As String
    Dim ws As Worksheet
    Dim codeToRestore As String
    Dim vbComp As Object

    ' Specify the sheet where the code is located.
    Set ws = ThisWorkbook.Sheets("Débiteurs") ' Replace with the name of your sheet.

    ' Retrieve the code module associated with the sheet.
    Set vbComp = ThisWorkbook.VBProject.VBComponents(ws.CodeName)

    ' Define the code to be restored.
    codeToRestore = "Private Sub Worksheet_SelectionChange(ByVal Target As Range)" & vbCrLf & _
                    "    If Target.Cells.Count > 1 Then Exit Sub" & vbCrLf & _
                    "    If Intersect(Target,  deb].Columns(1)) Is Nothing Or Target.Value = hdeb].Cells(1).Value Then Exit Sub" & vbCrLf & _
                    "    Cancel = True" & vbCrLf & _
                    "    Sheets(""Data"").Activate" & vbCrLf & _
                    "     Data].AutoFilter Field:=1, Criteria1:=Target.Value" & vbCrLf & _
                    "End Sub"

    ' Check the code of the `Worksheet_SelectionChange` procedure.
    code = vbComp.CodeModule.Lines(1, vbComp.CodeModule.CountOfLines)

    ' Clear the current code.
    vbComp.CodeModule.DeleteLines 1, vbComp.CodeModule.CountOfLines

    ' Restore the code.
    vbComp.CodeModule.InsertLines 1, codeToRestore

End Sub