CODEWITHSUNDEEP
vbaexcel
Ethan V
Ethan V

Reputation: 51

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

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

Upvotes: 1

Views: 3577

Answers (3)

Tim Williams
Tim Williams

Reputation: 166241

Private Sub RefreshButton_Click()

    On Error Goto haveError

    Refreshbuttons
    Application.EnableEvents = False
    ActiveWorkbook.RefreshAll

    haveError:
    Application.EnableEvents = True

End Sub

Upvotes: 1

Mr. Mascaro
Mr. Mascaro

Reputation: 2733

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.

Upvotes: 0

Jur Pertin
Jur Pertin

Reputation: 564

Declare Flag as global like below. It should work.

Public Flag as Boolean

Upvotes: 0

Related Questions