Skip to Content
avatar image
Former Member

Disable the Refresh and Save Data tab in excel ribbon pane

I am trying to disable the Refresh and Save Data tab in EPM excel ribbon pane since already created custom macro button for both button. Currently i am able to suppress both tab by below vba code. The problem now is that in the BEFORE_REFRESH event, when clicking at the standard  Refresh button in excel ribbon pane the system pop up "Process in progress. Please wait........"  and its tricky to press msgbox. Is there a way stoping the process of refreshing and prompt the msgbox . Appreciate input 😊

Dim blnMySave As Boolean
Dim blnMyRef As Boolean


Sub REFRESH()
Dim ea As New EPMAddInAutomation
        Dim selectd As String
    blnMyRef = True
    ea.RefreshActiveWorkBook
    blnMyRef = False
End Sub

Sub SEND()
Dim ea As New EPMAddInAutomation
        Dim selectd As String
    blnMySave = True
    ea.SaveAndRefreshWorksheetData
    blnMySave = False
End Sub

Public Function BEFORE_SAVE() As Boolean

If blnMySave Then

    BEFORE_SAVE = True

Else
      
    MsgBox "Please use SAVE DATA button on the worksheet!"

    BEFORE_SAVE = False

End If

End Function

Public Function BEFORE_REFRESH() As Boolean

If blnMyRef Then

    BEFORE_REFRESH = True

Else
   
    MsgBox "Please use Refresh button on the worksheet!"

    BEFORE_REFRESH = False

End If

End Function

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Mar 04, 2014 at 08:32 AM

    The code with the timer to show message for refresh:

    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

    Dim blnMySave As Boolean

    Dim blnMyRef As Boolean


    Public Function BEFORE_SAVE() As Boolean

        If blnMySave Then

            BEFORE_SAVE = True

        Else

            MsgBox "Please use Save button on the worksheet!"

            BEFORE_SAVE = False

        End If

    End Function


    Public Function BEFORE_REFRESH() As Boolean

        If blnMyRef Then

            BEFORE_REFRESH = True

        Else

            'Run timer with 100 msec interval

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

            BEFORE_REFRESH = False

        End If

    End Function


    Public Sub MySave()

        blnMySave = True

        epm.SaveAndRefreshWorksheetData

        blnMySave = False

        MsgBox "Saved"

    End Sub


    Public Sub MyRefresh()

        blnMyRef = True

        epm.RefreshActiveWorkBook

        blnMyRef = False

        MsgBox "Refreshed"

    End Sub


    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 use Refresh button on the worksheet!"

        End If

    End Sub

    Vadim

    Add comment
    10|10000 characters needed characters exceeded