How to block/disable Refresh All from the excel 20

Question

I have given a button to the user to populate the data from sql oledb connections. That does the job so I can control when user should populate the data from query tables. My problem is, I have no way of blocking user pressing ‘Refresh All ‘ button from the ribbon.

Tried the following code in Public Sub Workbook_Open() '

        '-- Turn off the Refresh Capability from Ribbon
        For J = 1 To ActiveWorkbook.Connections.Count

           With ActiveWorkbook.Connections(J).OLEDBConnection
             .BackgroundQuery = False
             .RefreshOnFileOpen = False
           End With

          'MsgBox ActiveWorkbook.Connections(J).Name
        Next J

It doesn’t work. 'Refresh All' is still active and populates all worksheets.

Please let me know how to block ‘Refresh All’ from the ribbon or a way Not Refresh, when user presses that button.

Thanks.

Answer 1

I think your only option may be to try and disable the Refresh All button, but I've never done anything like that.

This thread seemed to show a way to do it.
This site by Ron de Bruin has a lot of helpful information.
And this SO question has some helpful information.

Good luck!