Excel 365 - Private Sub Worksheet_Change (ByVal Target As Range) not working/triggering

[John S Clark]

Hi,

I have an old excel file (XLSB) that I wrote to help others understand how VLookups actually work, as I kept getting the same questions about 'Why is it not working this time when I have done everything the same', this will have been created in about 2013/2014.
Within the file I have a explanation sheet, which contains user selectable/editable Lookup Values/ TableArrays / Column Indexes and code sitting in the background to change the visual explanation beneath the fields. This worked a treat, however having shared the file with others today, I have found the sheet code which triggers the background code to change the visuals is no longer triggering and I dont understand why. Can you give me any pointers?

Macro that changes the cell formatting / visual explanation = 'VLookUpExplaination'
- Note this code runs perfectly well, when triggered manually - it successfully picks up the user entered values and updates the explanation further down the sheet.

Sheet Code (Bit that is not working)

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$C$2" Then
'MsgBox "LookupValue value modified"
Call VLookUpExplaination
Range("C3").Select

End If

If Target.Address = "$D$2" Then
'MsgBox "TableArray value modified"
Call VLookUpExplaination
Range("D2").Select
End If

If Target.Address = "$E$2" Then
'MsgBox "Column Index value modified"
Call VLookUpExplaination
Range("E2").Select
End If

End Sub

I can confirm, Marcos are enabled, the code behind "VLookUpExplaination" works as expected and does not error - its only the worksheet trigger that is the issue. No errors are given, I have tried enabling Events as I saw that on a different post, but that has not helped either.

Any support will be greatly appreciated.

 

[RoryA]

Have you checked that events are enabled? Try running:

application.enableevents = true

in the Immediate Window.

 

[John S Clark]

Have you checked that events are enabled? Try running:

application.enableevents = true

in the Immediate Window.

Hi RoryA,

Thank you for the suggestion, I have tried this and it makes no difference.

 

[RoryA]

If you re-enable the Msgbox lines, are you seeing the messages or is the code just not being triggered at all? Also, how are you changing the values in C2:E2?

 

[John S Clark]

If you re-enable the Msgbox lines, are you seeing the messages or is the code just not being triggered at all? Also, how are you changing the values in C2:E2?

Hi RoryA,

I have re-enabled the MsgBox(es) and these are not firing either.
In terms of how I am updating the cells on the sheet
- C2 = Manual text written into the cell (Text in this instance)
- D2 = Selection from pre-defined list, achieved by data validation on cell referencing a predefined list of values
- E2 = Manual text written into cell (number in this instance)

 

[RoryA]

OK, then assuming events are actually enabled, that should be working. Can you just add this code and run it once to be sure:

Sub ResetEvents()
application.enableevents = true
msgbox application.enableevents
End Sub

And confirm that you see True as the message. If you do, can you then test your original code again, please.

 

[John S Clark]

OK, then assuming events are actually enabled, that should be working. Can you just add this code and run it once to be sure:

Sub ResetEvents()
application.enableevents = true
msgbox application.enableevents
End Sub

And confirm that you see True as the message. If you do, can you then test your original code again, please.

Hi RoryA,

That code returned the result 'True'.

I have retested and the sheet code is still not trigger, I am not getting the msgboxes or the outcome of the VLookUpExplaination code.

 

[John S Clark]

Hi RoryA,

That code returned the result 'True'.

I have retested and the sheet code is still not trigger, I am not getting the msgboxes or the outcome of the VLookUpExplaination code.

RoryA,

Thank you for your assistance so far, I suspect something helped, as I did another PC restart (after the above) and it's back working again.
God knows what was wrong with it initially and why that carried on after an initial reboot and the enablement of events etc. I even created a new XL workbook and recreated the sheet (before the reboot) which also did not work. But for some reason, and I am thankful for it, another OS restart has seemingly sorted it out.