Force a Worksheet Filter on a Planning View Template (even after initial opening)

[Verheyen-Vincent]

Please find the following code below, which throws an error if the worksheet filter of "SHEETABC" is not equal to "Product ID = XYZ".

However, my question is with regards to BEFORE_REFRESH(), in case the filter would be not equal (so in the case of If Not SOPFilterValueIsValid() Then

You will notice that, to exit the BEFORE_REFRESH() Function, one needs to click on an area outside of the "Process in progress. Please wait..." dialog. How so please? How to prevent this necessity of clicking? In fact, I just want to exit the Function immediately/automatically in this case.

`' Custom VBA code with VBA Hooks for SAP IBP add-in for Microsoft Excel
' Force a Planning Filter
' by Vincent Verheyen, 2024-02-29

Private IBPAutomationObject As Object

Function IBPBeforeSend(callMode As String) As Boolean
If callMode = "SAVE" Or callMode = "SIMULATE" Or callMode = "CREATE_SIMULATION" Then
' SOPFilterValueIsValid
If Not SOPFilterValueIsValid() Then
IBPBeforeSend = False
Exit Function
End If
End If
IBPBeforeSend = True
End Function

Function BEFORE_REFRESH() As Boolean

' SOPFilterValueIsValid
If Not SOPFilterValueIsValid() Then
BEFORE_REFRESH = False
Exit Function
Else
BEFORE_REFRESH = True
Exit Function
End If

End Function

Function SOPFilterValueIsValid() As Boolean
Dim ws As Worksheet
Dim sopFilterValue As Variant
Dim mandatoryFilter As String
Const SheetName As String = "SHEETABC" ' Define SheetName as a constant
mandatoryFilter = "Product ID = XYZ" ' Set the mandatory filter value

' Set the worksheet where the formula is located
Set ws = ThisWorkbook.Sheets(SheetName)

' Get the value of the cell containing the formula @INDEX(SOP_Filter,1)
sopFilterValue = ws.Evaluate("=@INDEX(SOP_Filter,1)")

' Check if filter matches MandatoryFilter
If sopFilterValue <> mandatoryFilter Then
MsgBox "The worksheet """ & SheetName & """ has the mandatory requirement of including the filter """ & mandatoryFilter & """." & vbCrLf & vbCrLf & _
"The current value of that worksheet's filter is """ & sopFilterValue & """." & vbCrLf & vbCrLf & _
"Please assign the filter """ & mandatoryFilter & """ to worksheet """ & SheetName & """.", vbCritical
SOPFilterValueIsValid = False
Else
SOPFilterValueIsValid = True
End If
End Function
`