PDA

View Full Version : [SOLVED] Excel Worksheet_SelectionChange event not firing at all? (on both Office 2013 & 2016)



zredbaron
02-07-2017, 09:27 AM
I've been having a heck of a time accomplishing what I thought would be an incredibly simple test. All I am trying to achieve is to pop up a MsgBox when a user selects a new cell or changes the contents of a cell.

I've been at this for about 6 hours and so far have zero success! I have identical behavior with Office 2016 (Windows 10) and with Office 2013 (Windows 7).

Here are my method(s):


Create a new macro-enabled workbook.
Record a new macro in the workbook. Stop the recording. Open VBA.
Open the code for "Module 1" and replace the undesired code with the code below. Save the file.
File -> Options -> Trust Center -> Trust Center Settings -> Macro Settings -> "Trust access to the VBA project object model" is selected. Save the file.
I also have ensured Application.EnableEvents = True
I am expecting to be able to click on various cells, or edit cells, and received a MsgBox whenever the event occurs.


Here is my code:

Option Explicit 

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
MsgBox "changed!"
End

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "selected!"
End Sub

Public Sub Just_In_Case()
Application.EnableEvents = True
End Sub


What am I missing? Is there a security setting preventing this action event? I have the same behavior online at work as I do offline at home.


Thank you in advance for your help! :)

PS Here is the screenshot of my VBA environment, if relevant:
18272

Paul_Hossler
02-07-2017, 11:29 AM
You have the code in a Standard Module


This is a Workbook event and goes in the ThisWorkbook module and applies to all changes in all worksheets




Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
MsgBox "worksheet changed! " & Sh.Name
End Sub



18278

This is a worksheet event and goes in the module of the worksheet to which it applies. It only applies to that worksheet



Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "selected " & Target.Address
End Sub



18277

zredbaron
02-07-2017, 11:48 AM
You have the code in a Standard Module

This is a Workbook event and goes in the ThisWorkbook module and applies to all changes in all worksheets


That did it! I thank you, kind sir! :thumb