Skip to Content
Author's profile photo Badrish Shriniwas

Input Schedule Validation using EPM Add-in Events

Business Case

Validating the input schedule before submitting data to BPC 10 via EPM add-in for excel.  The sample scenario taken here is that set of cells should not exceed 5000 for the data to get saved.

If it exceeds 5000, EPM add-in save option should give out an error message and not save the data.


Here is an example of an input schedule with the operating expense. Cell B1 has been given a name (named range)  of “rng_Validation”. Cell B1 also contains a formula to validate the above business scenario. The cell returns a value 0 if the validation is not met. The cell returns a value of 1, if the validation is successfully met. 


Go to VBA development screen (shortcut Alt + F11) >> Add a new module and paste the following lines.


Function BEFORE_SAVE()

If Range(“rng_Validation”) = 0 Then

    MsgBox “Please correct the numbers before saving”, vbCritical

    BEFORE_SAVE = False


    BEFORE_SAVE = True

End If

End Function


If the data in the range B4:D4 is greater than 5000 then while saving the data the following error will be displayed and data will not be saved. Subsequently when the error is corrected the data will be saved without any message.


Other Options

Since this approach requires least amount of knowledge about the back end, this can be easily implemented. In BPC NW, other options like Write-back badi or UJ_VALIDATIONS can be explored. 

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Very Well said Badrish

      Author's profile photo Arun Kumar Suryanarayana Rao
      Arun Kumar Suryanarayana Rao

      Thanks Badrish, This is a very common requirement, it will be very useful.

      Author's profile photo Former Member
      Former Member

      Hi Badrish,

      Great guide. We had implemented something similar. I have a suggestion to this though. In your example, B4:D4 is hardcoded. You can make that dynamic by using the OFFSET function.


      (Change 1000 and ZZ as per row and column requirements based on estimates)

      In your example, A3 is the point of intersection for the rows and the columns, it will never change.

      I am starting the COUNTA from the first row and column member respectively. So, even if the number of row or column changes, the table will be dynamic enough to consider the change.

      Thanks for the tutorial.


      Author's profile photo Ravindra Tumuluri
      Ravindra Tumuluri

      Good one !! Thanks

      Author's profile photo Former Member
      Former Member


      Author's profile photo Former Member
      Former Member

      Will be useful in my project.Thanks a lot!

      Author's profile photo Yashvi Maheshwari
      Yashvi Maheshwari

      It is helpful ..!!

      Author's profile photo Former Member
      Former Member

      Question, I'm trying to make a different function based on whether the user choose Save Workbook or Save Worksheet, I'm using the BEFORE SAVE function well for worksheet but I want a different set of validation checks to be performed if the user selects the Save Workbook selection via the Save Data EPM ribbon area.

      Any suggestions?


      Author's profile photo Badrish Shriniwas
      Badrish Shriniwas
      Blog Post Author

      Do you want this validation to work globally on all the input schedules?

      Author's profile photo Former Member
      Former Member

      Hi Shriniwas,

      I am trying to use the same logic but my requirement little different I am using this funtion on text, I am comparing two cells values they must be equal the only the user must be able to save the data. There are over 200 rows where I am trying to apply this logic. Is there any way to execute this same logic in my case.