Only allow input on lower level (Planning View should contain at least certain Attributes) for editable Key Figure

[Verheyen-Vincent]

Hi all,

I would like to restrict the editing of a certain Key Figure on an aggregated level.

Therefor, please find the below VBA code.

However, unfortunately, it currently only checks whether or not the Planning View is being simulated / saved ... and if that certain Key Figure is present.

It does not specifically checks whether or not there have been any specific changes (edits) to that particular editable Key Figure. How would this be possible please?

`
Private IBPAutomationObject As Object

Function IBPBeforeSend(callMode As String) As Boolean
If callMode = "SAVE" Or callMode = "SIMULATE" Or callMode = "CREATE_SIMULATION" Then

    Dim Target As Range
    Dim cell As Range
    Dim searchString As String
    Dim cellValue As String
    Dim keyFigureFound As Boolean
    Dim custIDFound As Boolean
    Dim prdIDFound As Boolean
    Dim errorMessage As String
    
    Set Target = ActiveSheet.UsedRange
    
    For Each cell In Target
        If Not IsEmpty(cell) Then
            cellValue = cell.Formula
            searchString = searchString & cellValue
            ' Check if Key Figure "ZZZEXAMPLEOFAKEYFIGURE)" is present.
            If InStr(1, cellValue, "EPMOlapMemberO(""[KEY_FIGURES].[].[ZZZEXAMPLEOFAKEYFIGURE]") > 0 Then
                keyFigureFound = True
            End If
            ' Check if Customer ID attribute is present
            If InStr(1, cellValue, "EPMOlapMemberO(""[CUSTID].[].[") > 0 Then
                custIDFound = True
            End If
            ' Check if Product ID attribute is present
            If InStr(1, cellValue, "EPMOlapMemberO(""[PRDID].[].[") > 0 Then
                prdIDFound = True
            End If
        End If
    Next cell
        
    ' If Key Figure is present
    If keyFigureFound Then
        ' Check if attributes "Customer ID" and "Product ID" are present in the Planning View.
        If Not custIDFound And Not prdIDFound Then
            errorMessage = "s ""Customer ID"" and ""Product ID"""
        ElseIf Not custIDFound Then
            errorMessage = " ""Customer ID"""
        ElseIf Not prdIDFound Then
            errorMessage = " ""Product ID"""
        End If
        
        ' Display the error message
        If errorMessage <> "" Then
            MsgBox "Please add the Attribute" & errorMessage & " to the Planning View." _
            & vbNewLine _
            & vbNewLine & _
            "Error:" _
            & vbNewLine _
            & vbNewLine & _
            "Edits on Key Figure ""ZZZEXAMPLEOFAKEYFIGURE"" should not be made on a higher level. " & _
            "Both the attributes ""Customer ID"" and ""Product ID"" should be present in the Planning View.", vbOKOnly
            IBPBeforeSend = False
        Else
            IBPBeforeSend = True
        End If
    Else
        ' If Key Figure is not present
        IBPBeforeSend = True
    End If
    
End If

End Function
`

[PW25]

Unfortunately, there's no VBA method that returns all changed key figure values, so you can check whether the key figure in question actually got changed. At the moment there are no concrete plans to provide further VBA methods.