Force a Worksheet Filter on a Planning View Template (even after initial opening) #13
Unanswered
Verheyen-Vincent
asked this question in
Q&A
Replies: 0 comments
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
-
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.
![process in progress](https://private-user-images.githubusercontent.com/113923740/308976323-92d4e8d6-d537-4432-8b10-af8f583251fd.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MjA4MzA4OTMsIm5iZiI6MTcyMDgzMDU5MywicGF0aCI6Ii8xMTM5MjM3NDAvMzA4OTc2MzIzLTkyZDRlOGQ2LWQ1MzctNDQzMi04YjEwLWFmOGY1ODMyNTFmZC5wbmc_WC1BbXotQWxnb3JpdGhtPUFXUzQtSE1BQy1TSEEyNTYmWC1BbXotQ3JlZGVudGlhbD1BS0lBVkNPRFlMU0E1M1BRSzRaQSUyRjIwMjQwNzEzJTJGdXMtZWFzdC0xJTJGczMlMkZhd3M0X3JlcXVlc3QmWC1BbXotRGF0ZT0yMDI0MDcxM1QwMDI5NTNaJlgtQW16LUV4cGlyZXM9MzAwJlgtQW16LVNpZ25hdHVyZT0zZTM1YTdjZTQyYjc0NTUzMGQ4NmQ3NGVlMjRjNzcwYTVjZjIwNTAwNzA4MTJiZDgzNzgwNjFiNjU3YjVjMDdlJlgtQW16LVNpZ25lZEhlYWRlcnM9aG9zdCZhY3Rvcl9pZD0wJmtleV9pZD0wJnJlcG9faWQ9MCJ9.kGcJUbyXRpfJdtOWlNMqIpiyYxKyE0ZJBP7CiNkiLtI)
`' 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
`
Beta Was this translation helpful? Give feedback.
All reactions