Overview

Former Member · ‎2012 Apr 06

This document demonstrates how to create a custom ribbon with menu buttons in BPC Input Schedules. It includes use of Custom UI Editor for Microsoft Office along with VBA coding.

Overview

SAP BPC N/W 7.5 version does not have any standard functionality to add a custom ribbon. There is a How to document by SAP (Creating Custom Menu within BPC NW) which describes more about adding custom menu buttons in the Input Schedules layout. Whereas, this paper explains how to add a custom Ribbon with menu buttons in BPC Input Schedule using Custom UI Editor for Microsoft Office. In some business cases, standard BPC Functions such as expand, refresh, send data, update dimension members etc. can be triggered by adding menu buttons on the custom ribbon using VBA code.

Add Custom Ribbon on Input Schedule Layout

In order to add a custom ribbon, we need to download Custom UI Editor for Microsoft Office. It can be downloaded from the link below. http://openxmldeveloper.org/blog/b/openxmldeveloper/archive/2009/08/06/7293.aspx
Save your BPC Input Schedule as a macro-enabled file with .xlsm extension, example Test_Ribbon.xlsm and close the file.
Start the Custom UI Editor and go to File menu and click on Open button and select your file.
Right-click on the file name in the left pane, and then select Office 2010 Custom UI Part as shown below. This adds a customUI14.xml part to the document.

5. A sample code is shown below. Customize the code as per your requirements in the right pane.

<tabs>

</group>

<button id="DDSelect" label="Select" imageMso="SelectionProperties" size="large" keytip="P"

onAction="User_Selections" />

</group>

<menu id="DDPromo" label="Promotions" imageMso="AdministrationHome" size="large"

keytip="F">

<button id="MbtnDeleteP" label="Delete Promotion"

onAction="Delete_Promo" />

</menu>

<button id="MbtnUpdate" label="Update to CRM" imageMso="AddContentType"

onAction="UpdateToCRM" size="large" keytip="H" />

</group>

<button id="MbtnSave" label="Save" imageMso="SaveItem" onAction="Save_Data" size="large"

keytip="WS"/>

<button id="MRef" label="Refresh" imageMso="Refresh" onAction="Refresh_Data" size="large"

keytip="WR"/>

</group>

</tab>

</tabs>

</ribbon>

</customUI>

6. This code performs the following actions:

a. First, it adds a custom tab to the Excel sheet. Code to add it is mentioned below

<tabs>

</tab>

</tabs>

</ribbon>

</customUI>

b. Then, it adds menu buttons to the custom tab. These buttons can be drop down as well.

<button id="BtnSelect" label="Select" imageMso="SelectionProperties" size="large" keytip="P"

onAction="User_Selections" />

</group>

<menu id="DropDown" label="Promotions" imageMso="AdministrationHome" size="large”

keytip="F">

<button id="MbtnDeletePromo" label="Delete Promotion" onAction="Delete_Promo"

</menu>

</group>

c. There are two types of image attributes that can be used to add images on the buttons on the Ribbon:

The imageMso attribute is used to add images that are built into Microsoft Office. Mention the name of the image to be displayed and its size.

e.g.: imageMso="SelectionProperties" size="large"

The list of built-in images can be downloaded from here.

(http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=21103)

The image attribute is used to add custom images to the Ribbon. In the Custom UI Editor, go to Insert menu and click on Icons button to insert the image. You can see it now below the customUI file.

e.g.: image="Custom_Image" size="large"

d. The onAction attribute specifies the macro that is executed when you click the button.

e.g.: <button id="BtnLogOn" onAction="BPC_LogOn"

7. Once the code is ready, click the Validate button on the toolbar to verify that your .xml is free from errors. When

the .xml is error free you will receive a message stating that the code is well formed.

8. Save and then close the editor.

Add VBA Code to the Test_Ribbon.xlsm file

Open the Test_Ribbon.xlsm file. The “Custom Ribbon” tab that you created should now be displayed.

2. Click the Developer tab and then click on Visual Basic. This opens the Visual Basic Editor.

3. In left pane, right-click on VBAProject(Test_Ribbon.xlsm) node. Click on Insert and select Module.

4. Add the following procedures to the inserted module. The name of the procedure should be same as mentioned in the onAction part of the .xml code.

For example, for BPC Logon Button, the corresponding macro is called as mentioned below.

Custom UI code:

<button id="BtnLogOn" onAction="BPC_LogOn"

VBA Code appendix

'For the Log On to BPC Button

Sub BPC_LogOn(control As IRibbonControl)

Dim myval As Boolean

Dim AI As Excel.AddIn

On Error Resume Next

myval = Application.AddIns("Ev4Excel.xla").Installed

On Error GoTo 0

If myval Then

Set g_clsExcel = GetEVExcel2007()

g_clsExcel.Logon 1

Else

Set AI = Application.AddIns.Add(Filename:="C:\Program Files\SAP BusinessObjects\PC_NW\ Ev4Excel.xla")

AI.Installed = True

End If

End Sub

'For the Select Button

Sub User_Selections(control As IRibbonControl)

Selection.Show

End Sub

'For the Save Button

Sub Save_Data (control As IRibbonControl)

Application.Run "MNU_eSUBMIT_REFSCHEDULE_BOOK_NOACTION"

Application.Run "MNU_eTOOLS_EXPAND"

Application.Run "MNU_eTOOLS_REFRESH"

End Sub

'For the Refresh Button

Sub Save_Data (control As IRibbonControl)

Application.Run "MNU_eTOOLS_EXPAND "

End Sub

Adding Custom Ribbon to SAP BPC Input Schedules

Overview

SAP Access Control - Useful Documents, Blogs, Resources, etc.

Helpful transactions, tools, programs, tables, etc. for a SAP GRC Consultant

Configure Emergency Access (EAM) in GRC 10