IBP - VBA - BEFORE_REFRESH() how to exit 'Process in progress. Please wait...' dialog w/o click

[vincentverheyen]

Dear experts, I have created a custom VBA code which checks if a certain Planning View filter is present in a certain Worksheet of a Planning View.

It checks this using SOPFilterValueIsValid before refreshing the Planning View, using Function BEFORE_REFRESH()

See also Planning with Microsoft Excel > ... > SAP IBP Hooks (VBA Events) > BEFORE_REFRESH.

Function BEFORE_REFRESH() As Boolean

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

 My question is: how to exit this Function in VBA completely without the need for an additional click anywhere in the MS Excel application (outside the 'Process in progress. Please wait...' dialog box). This click seems currently necessary unfortunately with the above code.

Please note: if SOPFilterValueIsValid is False, then we do not want to Refresh the Planning View (we do not want to 'apply' any refreshing with a wrong filter). This behaviour is already correct according to the above code. 

[riyazahmed_ca2]

Hi Vincent,

May you try this below piece of code.

Private IBPAutomationObject As Object

Function BEFORE_REFRESH()
    Dim attributeValues() As String
    
    On Error GoTo ErrorHandling:
        If IBPAutomationObject Is Nothing Then
            Set IBPAutomationObject = Application.COMAddIns("IBPXLClient.Connect").Object
            
            attributeValues = IBPAutomationObject.GetFilterValues(ActiveSheet)
    
            If Trim(attributeValues) = vbNullString Then
                'BEFORE_REFRESH = False
                MsgBox "Invalid Filter", vbOKOnly
                Exit Function
            Else
                'BEFORE_REFRESH = True
                Call IBPAutomationObject.Refresh
            End If
 
ErrorHandling:
    MsgBox Err.Description, vbOKOnly

End Function

Best Regards,

'Riyaz'

[vincentverheyen]

@riyazahmed_ca2 Thank you, I will explore the code and provide you feedback. There is an 'End If' missing it seems.

[riyazahmed_ca2]

@vincentverheyen 'EndIf' for IBPAutomationObject check and assignment is not mentioned in any sample codes. I can understand that it is not inline with VBA syntax, yet I din't get any errors.

[vincentverheyen]

Hi @riyazahmed_ca2 I get a "Compile error: Block IF without End If".