Worksheet.RefreshAll triggers Worksheet_changed Event - how do I stop this from happeing?

Question

I have an excel worksheet that is being used as a database front end for access. When a user changes data in a cell it will run the Worksheet_changed event to run codes that updates the access database.

However - I also have a refresh button that, you guessed it, refreshes the spreadsheet. This also causes the worksheet_changed event to run which will sometimes error out the program.

Private Sub RefreshButton_Click()

Refreshbuttons
ActiveWorkbook.RefreshAll

End Sub

How do I stop the work sheet changed event from happening when the refresh button is pressed? I have tried a Boolean flag which will stop the worksheet changed event from running when refreshed button is pressed - but it stops it from running at all (example of what I did)

Private Sub RefreshButton_Click()

Dim Flag as Boolean

Flag = True

Refreshbuttons
ActiveWorkbook.RefreshAll

Flag = False 

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Flag = true then
Exit Sub
Else

[...] {Rest of code below here}

I am stuck - any help is greatly appreciated!!!

Thanks,

Ethan

EDIT Thanks Tim! you pointed me in the right direction. I ended up going with (code below) and it worked beautifully. I appreciate everyones help!

Private Sub RefreshButton_Click()

For Each objConnection In ThisWorkbook.Connections
    'Get current background-refresh value
    bBackground = objConnection.OLEDBConnection.BackgroundQuery

    'Temporarily disable background-refresh
    objConnection.OLEDBConnection.BackgroundQuery = False

    'Refresh this connection
    objConnection.Refresh
    If ActiveSheet.FilterMode = True Then
     ActiveSheet.ShowAllData
        Else
    End If
    'Set background-refresh value back to original value
    objConnection.OLEDBConnection.BackgroundQuery = bBackground
Next

MsgBox "Refresh Complete"

End Sub

score 1 · Accepted Answer · answered Oct 13 '14 at 17:38

1

Private Sub RefreshButton_Click()

    On Error Goto haveError

    Refreshbuttons
    Application.EnableEvents = False
    ActiveWorkbook.RefreshAll

    haveError:
    Application.EnableEvents = True

End Sub

answered Oct 13 '14 at 17:38

Tim Williams

154,628
8
97
125

I tried using the EnableEvents like you both said but it will still run the worksheet changed event - if i just have `Application.EnableEvents = False` at the beginning of the stub it will stop it from running. But as soon as I put the `Application.EnableEvents = True` at the end of the sub it will cause the worksheet changed event to run – Ethan V Oct 13 '14 at 18:16
@EthanV: Insert a new module and add this: Public Flag as Boolean and delete Dim Flag as Boolean from Private Sub RefreshButton_Click() Let me know if this does not work – Jur Pertin Oct 13 '14 at 18:25
See this question and responses: http://stackoverflow.com/questions/22083668/wait-until-activeworkbook-refreshall-finishes-vba If you have queries in the workbook which are set to run in the background then `RefreshAll` will return before the query is completed, so your event handlers are re-enabled before the data actually gets updated. You'll need to do a bit more work to handle this. – Tim Williams Oct 13 '14 at 18:30

score 0 · Answer 2 · answered Oct 13 '14 at 17:28

0

Declare Flag as global like below. It should work.

Public Flag as Boolean

answered Oct 13 '14 at 17:28

Jur Pertin

574
4
9

score 0 · Answer 3 · answered Oct 13 '14 at 17:38

0

All office applications have a built-in function to control this behavior. Simply add: Application.EnableEvents = False to the beginning of the RefreshButton_Click event and Application.EnableEvents = True to the end of the event.

answered Oct 13 '14 at 17:38

Mr. Mascaro

2,693
1
11
18

Worksheet.RefreshAll triggers Worksheet_changed Event - how do I stop this from happeing?

3 Answers3