Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

VBA macro not working

Current system versions:

SAP_BW : 740

CPMBPC : 810

EPM add in : Version 10.0 SP 20 .NET 3.5

Issue:

Following macro is written:

Function BEFORE_REFRESH()

If Range("validation") > 1 Then

    MsgBox "Please reduce the selection", vbCritical

    BEFORE_REFRESH = False

Else

   

    BEFORE_REFRESH = True

End If

End Function

This is not running as it is supposed to i.e. not throwing the msgbox for the condition specified

"validation" is name assigned to a cell.

FPMXLClient and FPMXLClient_Olaputilities are activated in references.

Tags:
Former Member
replied

The following code will work:

Option Explicit

' Set WinAPI Timer

Public Declare Function SetTimer Lib "user32" ( _

    ByVal hwnd As Long, _

    ByVal nIDEvent As Long, _

    ByVal uElapse As Long, _

    ByVal lpTimerFunc As Long) As Long

' Kill WinAPI Timer

Public Declare Function KillTimer Lib "user32" ( _

    ByVal hwnd As Long, _

    ByVal nIDEvent As Long) As Long

' Global var for timer

Public lngTimerID As Long

Dim epm As New FPMXLClient.EPMAddInAutomation

Public Function BEFORE_REFRESH() As Boolean

    If ThisWorkbook.Worksheets("YourWorksheetName").Range("validation") > 1 Then

        'Run timer with 100 msec interval

        lngTimerID = SetTimer(0&, 0&, 100&, AddressOf TimerProc)

        BEFORE_REFRESH = False

    Else

        BEFORE_REFRESH = True

    End If

End Function

Sub TimerProc(ByVal hwnd As Long, ByVal uMsg As Long, ByVal nIDEvent As Long, ByVal dwTimer As Long)

    If nIDEvent = lngTimerID Then

        KillTimer 0&, lngTimerID

        MsgBox "Please reduce the selection", vbCritical

    End If

End Sub

You can also test for ActiveWorksheet.Name="YourWorksheetName" to test for refresh of particular worksheet.

Vadim

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question