Disable the Refresh and Save Data tab in excel ribbon pane

[Former Member]

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

[former_member186338 · Accepted solution]

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

[Former Member · Accepted solution]

Hi Vadim,

When i apply the timer as per your code, it mess up my refresh and save data macro button. When click at refresh macro button it doesn't do anything and when click at the save data macro button it prompt error message as "Invoke or BeginInvoke cannot be called on a control until the window handle has been created". Any idea?

' 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 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
   
    'Run timer with 100 msec interval

    lngTimerID = SetTimer(0&, 0&, 100&, AddressOf TimerProc)
   
    MsgBox "Please use Refresh button on the worksheet!"

    BEFORE_REFRESH = False

End If

End Function

Public Sub REFRESH()
'Dim ea As New EPMAddInAutomation
'        Dim selectd As String
    blnMyRef = True
    epm.RefreshActiveWorkBook
    blnMyRef = False
   'MsgBox "Refreshed"
End Sub

Public Sub SEND()
'Dim ea As New EPMAddInAutomation
'        Dim selectd As String
    blnMySave = True
    epm.SaveAndRefreshWorksheetData
    blnMySave = False
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

[former_member186338 · Accepted solution]

Please correct the Save function (refresh is also invoked in save!):

Public Sub MySave()

    blnMySave = True

    blnMyRef = True

    epm.SaveAndRefreshWorksheetData

    blnMySave = False

    blnMyRef = False

    MsgBox "Saved"

End Sub

In my system the code is working correctly:

After pressing Refresh on Ribbon with some delay I have:

After pressing Save on Ribbon I have:

Pressing buttons on the sheet correctly refresh or save.

Vadim

[Former Member · Accepted solution]

Hi Vadim,

You're right, now the refresh and save data macro button able to perform as previously. Thanks a lot for your effort and time

[former_member186338 · Accepted solution]

And now you understand how to use API timer in this simple example, when you want to perform some action after event is completed.

Vadim

[SimonLH · Accepted solution]

Excellent piece of code - thanks

[Former Member · Accepted solution]

Hi Vadim,

I was trying out this piece of code and seems like the refresh isn't working, I have made changes to the Save function also as you advised below.

I have attached the piece of code, the concern is only with the refresh not working properly. I am a newbie with macros, so kindly advice.

Regards,

Shariq

Macro.txt.zip

[former_member186338 · Accepted solution]

Please read the discussion again and apply corrections in "correct answer"!