Workbook_Open event to set Context

[Former Member]

Hi,

In our BPC 10.0 NW system we have a menu page that includes Context Member selections (via EPMContextMember function) and links to launch other Input Schedules and Reports (via the EPMPathLink function).

Some of the input schedules that are launched from this page are based on a different model, so the context selections do not flow through when the schedule opens.  This is frustrating to users as they need to enter their selections again for the new model.

I have tried to overcome this by creating a macro based on the Workbook_Open event in the target schedule which finds the context selections for the model from the source file and updates the context in the report model via the SetContextMember API.

This is working fine for me, but when other users open the schedule, the context does not get updated.  It appears that the Workbook_Open macro does not run.  If I insert the same VBA code in a macro attached to a button, it works fine for other users when they click the button.

I have also tried creating an Auto_Open macro with the same code, which again appears to work fine for me but not for other users.

Any ideas why the Workbook_Open macro would not be running for other BPC users?  The macro is within the ThisWorkbook object of the relevant VBAProject file.

Thanks,

Matt

[Former Member · Accepted solution]

Hi,

I ended up resolving this issue by creating buttons to launch the various input schedules and reports.  The macro sets the context using the code that I created before and then opens the required workbook using the OpenSpecificDocument API.

This seems to work well for all users and avoids the need to manually press a button specifically to synchronise the context.

Thanks Shrikant for your input.

Cheers,

Matt

[Shrikant_Jadhav]

P.S.

Where do you placed this code? Code has to placed in "ThisWorkbook" module.

Shrikant

[Former Member]

Hi Shrikant,

We are using Excel 2010 version 14.0.7128.5000 (32-bit)

Macro security settings could be the issue, but my settings actually seem higher than other users for which it has not worked.  Mine is "Disable all macros with notification" and theirs is "Enable all macros".  So if anything I would have thought it would work for them and not me!

My code is in the ThisWorkbook module.

Cheers,

Matt

[Shrikant_Jadhav]

Hi Matt,

Can you provide you code?

Add the following line to your code and try

Application.EnableEvents=True

Workaround to your requirement is , use BEFORE_REFRESH function in target workbook, in that function past your code and activate the "Refresh Data in the file when opening".

Regards,

Shrikant   

[Former Member]

Thanks Shrikant,

My VBA code in the target workbook is:

I tried adding the Application.EnableEvents=True parameter, but this did not make any difference on the other users' machine.

Using BEFORE_REFRESH also didn't work.

It is really confusing why it isn't working, and must be due to differences in Excel settings between users.

In the end the workaround I am using is a button on the menu page that synchronises the context selections across the models based on what is entered in the selections.

Thanks for your assistance.

Cheers,

Matt

[Shrikant_Jadhav]

Hi Matt,

Which MS version you are using?

Check security level for Macro, is it "enable all macro" or not

Regards,

Shrikant.