Calling macro with change in epm context member selection

[Former Member]

Hi Experts,

I wish to call a macro whenever a context member in Category dimension is changed.

The macro will basically hide columns depending on the category the user chooses.

The macro works fine when there is a data validation rather than a context member selection. So basically any change in the context member cell, doesnt trigger an event in excel, unlike data validation. I have tried putting the macro inside after_contextchange as well, but doesnt seem to work.

Can I have any kind of help regarding this or any other alternative? I need the context member to be there because I am triggering the same from BPF.

Regards,

Shariq

The following is the macro -

Option Explicit

Dim EPMobject As New FPMXLClient.EPMAddInAutomation

Dim i As Long

Dim j As Long

Function AFTER_CONTEXTCHANGE()

Private Sub Worksheet_Change(ByVal Target As Range)

EPMobject.RefreshActiveSheet

Application.ScreenUpdating = False

    ActiveSheet.Unprotect Password:="password"

    Columns.EntireColumn.Hidden = False

    Columns(1).Hidden = True

    Columns(6).Hidden = True

    Columns(7).Hidden = True

    Columns(8).Hidden = True

    Columns(9).Hidden = True

For i = 18 To 54

    If Range("D6") = "F48" Then

        Columns(i).Hidden = True

        Worksheets("Calls").Range("J:Q").Locked = False

    End If

Next i

For i = 6 To 25

    If Range("D6") = "F84" Then

        Columns(i).Hidden = True

        Worksheets("Calls").Range("Z:AC").Locked = False

    End If

Next i

[former_member186338]

Sorry, but the combination:

Function AFTER_CONTEXTCHANGE()

Private Sub Worksheet_Change(ByVal Target As Range)

is absolutely incorrect!

You have to define function AFTER_CONTEXTCHANGE and not - Worksheet_Change

Function AFTER_CONTEXTCHANGE() has to be created in a new VBA module, not in the Worksheet module!

[former_member186338]

The rest of the code is also strange...

Start with something like:

Option Explicit

Dim EPMobject As New FPMXLClient.EPMAddInAutomation

Function AFTER_CONTEXTCHANGE()

Dim i As Long

Dim j As Long

If ThisWorkbook.ActiveSheet.Name = "Calls" Then

    EPMobject.RefreshActiveSheet

    '....

End If

End Function

[Former Member]

Thanks Vadim, but my actual requirement is to hide and lock columns depending on what the user selects in the context member, I am not to familiar with macros.

And any changes to the context member is not triggered in excel, but the same happens with data validation. So I cant seem to think of a solution here, seems like a similar problem as in the following link

EPMSelectMember OnChange Refresh report | SCN

[former_member186338]

"And any changes to the context member is not triggered in excel, but the same happens with data validation." - the only issue here is the incorrect code!

Function AFTER_CONTEXTCHANGE() - works fine, tested many times!

Create new VBA module put Function AFTER_CONTEXTCHANGE() in this module like:

Function AFTER_CONTEXTCHANGE()

MsgBox "Context Changed"

End Function

And you will see!

Vadim