Calling macro with change in epm context member selection

615 Views

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