0

I have a worksheet_change macro embedded in two sheets within my workbook. They are there to prevent anyone making changes to the sheets. However, I still want the data within the sheets to be refreshed every so often. This does not work.

Two sheets within the workbook are connected via a query to another workbook. Essentially those sheets are a copy of the sheets within the other workbook. I have embedded Code1 into the two worksheets. This is to prevent anyone making changes to the worksheet but still allow them to view the sheet and copy data from it. It brings up an message box and then undoes the change made by the user. This works fine and I am happy with it.

At the same time I want to be able to refresh the workbook so that the connected sheets are up to date with respect to the other workbook that they are connected to.

To do this I have added a button into the workbook called "Refresh". This button calls Code2. This was done with the intention of disabling events so that the worksheet_change macro is paused to allow for the data to be refreshed.

However, this does not work as the worksheet_change macro still works. I.e after clicking the button, the workbook is refreshed and then any update is undone and the message box is displayed - which isn't what I need.

CODE1

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range

' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("A1:Z1000")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
       Is Nothing Then

    With Application
        .EnableEvents = False
        .Undo
        .EnableEvents = True
    End With

    ' Display a message when one of the designated cells has been
    ' changed.
    ' Place your code here.
    MsgBox "DO NOT MODIFY THIS SHEET - Any necessary modifications should be made in 'Master Invoice Template' and this sheet will automatically be updated!"

End If
End Sub

CODE2

Sub refresh()

On Error GoTo ErrorHandler
Application.EnableEvents = False
ThisWorkbook.RefreshAll

ErrorHandler:
Application.EnableEvents = True

End Sub

I have scoured the internet for a solution and pretty much everything that I find points me in the direction of enableevents=false, but as described in my post this does not work. Do I need to change the method of solving my problem or am I doing something wrong within my code?

I suspect the undo line of code is causing the problem, but I am not sure!

Any help would be greatly appreciated!

M.Laszkowski
  • 39
  • 8
  • I would advice you to disable events at the start of the `worksheet_change` event and enabling them at the end: it's good to make it a habit, as it avoids endless loops when you change the sheet within the event. Have you tried stepping through the code, to see when the `worksheet_change` event is triggered? place a break point at the beginning of your code and then step through using `F8`. – Tim Stack Feb 15 '19 at 11:29
  • General suggestion: rather than EnableEvents, use a global variable that code2 sets and code1 checks. At the beginning of _change, say `if code2_running then exit sub` or some such. – cxw Feb 15 '19 at 11:54
  • WHEN does the change event get triggered? Something must be changed in that instance. Make sure events are disabled before that happens – Tim Stack Feb 15 '19 at 11:55
  • Thanks for your input. I will take that into consideration in the future. I have just stepped through the code and found something interesting. So when I click the button, the described problem occurs. The button is placed on a different worksheet to the ones in question. When I select the sheets that have the `worksheet_change` macro in them selected and then step through the code; the code works! But it doesn't when I am stepping through when the sheet with the button is selected. Any ideas? – M.Laszkowski Feb 15 '19 at 11:56
  • [Interesting Read](https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure/13861640#13861640) Use the Error Handler Logic in Code 1 as well. Also type `Application.EnableEvents = True` into the immediate window once and press enter to reset it so that your `Worksheet_Change` event can run. – Siddharth Rout Feb 15 '19 at 12:39
  • @SiddharthRout Thank you for your input. I have read the post in the link and have changed the Code1 to include the Error handler. The same problem is still occurring and I have no idea why! – M.Laszkowski Feb 18 '19 at 08:50
  • @TimStack When I step through the code using F8 the `worksheet_change` event doesn't get triggered. When testing I have been entering `Application.EnableEvents = True` into the immediate window before each test. Using this method, nothing gets triggered - however using the button still triggers the event? – M.Laszkowski Feb 18 '19 at 09:00
  • Did you type `Application.EnableEvents = True` into the immediate window (Press Ctrl + G) in VBE – Siddharth Rout Feb 18 '19 at 09:08
  • Yes I typed `Application.EnableEvents = True` in to the immediate window, and still the `Worksheet_change` event gets triggered – M.Laszkowski Feb 18 '19 at 09:46

1 Answers1

0

I think I have figured out what was wrong with the code; correct me if I am wrong. The data was taking too long to refresh when Code2 was ran. This meant that the Application.EnableEvents = Ture in Code2 took effect before the data could be fully refreshed and when it finally did complete its update, the Worksheet_Change event was triggered.

I tried using DoEvents after the RefreshAll command but this didn't work either. I have used what I found in this post to work around the problem and the refresh button now works!

Specifically the code that helped is below: I replaced Code2 with this:

Sub Refresh_All_Data_Connections()

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

    'Set background-refresh value back to original value
    objConnection.OLEDBConnection.BackgroundQuery = bBackground
Next

MsgBox "Finished refreshing all data connections"

End Sub

Please let me know if my logic in explaining why the code didn't work is correct - I am still new to VBA and would like to understand the problem fully!

M.Laszkowski
  • 39
  • 8