Sorry, but the issue is not clear!
1. Explain the required logic! "multiple selection filter" - ???? Provide step by step user actions and expected results!
2. Never attach images using "Insert File"
3. Post code using "Insert Code" - it's not possible to copy code from screenshot! Provide full code.
Just to test the following code is working absolutely fine on my computer:
Option Explicit Dim epm As New FPMXLClient.EPMAddInAutomation Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim strDimMembers As String Dim strConn As String If Target.Address(True, True, xlA1, False) = "$A$1" Then Cancel = True strConn = epm.GetActiveConnection(ActiveSheet) strDimMembers = epm.OpenMemberSelector(strConn, "CATEGORY", "") MsgBox strDimMembers End If End Sub
Option Explicit Dim epm As New FPMXLClient.EPMAddInAutomation Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim strCellDoubleClickAdr As String Dim strCellMemberListAdr As String Dim wshCurrent As Worksheet Dim strConnName As String Dim strDimName As String Dim strDimMembers As String Dim strDimMembersArr() As String Dim strFinalDimMembersArr() As String Dim strFinalDimMembersWDescArr() As String Dim strDimMembersWDesc As String Dim lngMaxMemberNum As Long Dim lngTemp As Long strCellDoubleClickAdr = "$E$9" If Target.Address(True, True, xlA1, False) = strCellDoubleClickAdr Then Cancel = True Set wshCurrent = ThisWorkbook.ActiveSheet strCellMemberListAdr = "$A$12" strConnName = epm.GetActiveConnection(ActiveSheet) strDimName = "SOC_DIV_PARCEIRA" strDimMembers = epm.OpenMemberSelector(strConnName, strDimName, "") strDimMembers = Left(strDimMembers, Len(strDimMembers) - 1) strDimMembersArr = Split(strDimMembers, ";") lngMaxMemberNum = UBound(strDimMembersArr) ReDim strFinalDimMembersArr(0 To lngMaxMemberNum) ReDim strFinalDimMembersWDescArr(0 To lngMaxMemberNum) For lngTemp = 0 To lngMaxMemberNum strFinalDimMembersArr(lngTemp) = Mid(strDimMembersArr(lngTemp), InStrRev(strDimMembersArr(lngTemp), "[") + 1, Len(strDimMembersArr(lngTemp)) - InStrRev(strDimMembersArr(lngTemp), "[") - 1) strFinalDimMembersWDescArr(lngTemp) = strFinalDimMembersArr(lngTemp) & " - " & epm.GetMemberCaption(strConnName, strDimMembersArr(lngTemp)) Next lngTemp strDimMembers = Join(strFinalDimMembersArr, ",") strDimMembersWDesc = Join(strFinalDimMembersWDescArr, ",") Target.Value = strDimMembersWDesc wshCurrent.Range(strCellMemberListAdr).Value = strDimMembers End If End Sub
Dear Vadim,
Thank you for answer. I have provided the full code as requested.
Regarding the questions you asked, please let me explain better
The goal of this VBA is to allow the user to select more than one member of the dimension, in the report filter.
We have implemented a logic that does the following:
1 - The user opens the report and double clicks the filter
2 - The VBA code is executed and opens a window where the user can select multiple dimension members
3 - After the selection, the VBA code writes the selection into excel cell $A$12
4 - We apply a "dimension members override" function to the report, to expand based on the selected members that are listed in cell $A$12
This allows the report to expand per multiple selection members.
The problem is that somewhere in time, this logic stopped working in our key-users computer, because after the members are selected and after click "ok", the macro gives the attached error.
Have I made myself clearer?
How should I attach the images?
Thank you :)
Raquel Oliveira
Add comment