Sub to detect any change in cell selection within any open workbook?

**Nickster64** · 12-03-2008, 12:42 PM

I know of

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

but I would like to expand this idea to detect any change in cell selection across all open workbooks.

Thanks!

Nick

**Leith Ross** · 12-03-2008, 12:52 PM

Hello Nickster64,

This can be done using the Application object event SheetChange. This will detect any change to cell made by the user or from a external link. To use this event requires creating a Class object that will sink (capture) the event.
The following is from the VBA Help files...

Before you can use events with the Application object, you must create a new class module and declare an object of type Application with events. For example, assume that a new class module is created and called EventClassModule. The new class module contains the following code.

Public WithEvents App As Application

After the new object has been declared with events, it appears in the Object drop-down list box in the class module, and you can write event procedures for the new object. (When you select the new object in the Object box, the valid events for that object are listed in the Procedure drop-down list box.)

Before the procedures will run, however, you must connect the declared object in the class module with the Application object. You can do this with the following code from any module.

Dim X As New EventClassModule

Sub InitializeApp()
Set X.App = Application
End Sub

After you run the InitializeApp procedure, the App object in the class module points to the Microsoft Excel Application object, and the event procedures in the class module will run when the events occur.

If you have any difficulty, let me know. It can be confusing.

Sincerely,
Leith Ross

**Nickster64** · 12-03-2008, 01:20 PM

Thanks Leith,

I understand the idea of capturing the event but am unclear on how it's all put together...

I created a new Class Module within my EventClassModule (Workbook Name). My code within this Class Module is:

Please Login or Register  to view this content.

I assume I would have to specify Excel as the application for which I want to capture events from but how do I do this?

Much thanks,

Nick

**Leith Ross** · 12-03-2008, 03:36 PM

Hello Nickster64,

To declare the Application as Excel, qualify the reference this way...

Please Login or Register  to view this content.

To access the SheetChange event, click on the Object List drop down (the one on the upper left of the code module window). Once you selected App, click on the Drop Down on the upper right to select the event. Excel will add the procedure code start and end lines automatically.

Sincerely,
Leith Ross

**Nickster64** · 12-03-2008, 03:58 PM

Thanks, I understand. How does this look? The purpose is to reset a timer used to trigger an autosave/shutdown process when a different workbook is activated.

Please Login or Register  to view this content.

**Leith Ross** · 12-03-2008, 04:59 PM

Hello Nickster64,

I take it that you have declared resetTimer in a standard VBA module. So far, it looks fine. Have you tried it yet?

Sincerely,
Leith Ross

**Nickster64** · 12-03-2008, 06:38 PM

I just tried it. Everything works flawlessly while working within the workbook. However, the timer continues to start if I switch to and begin work on a separate workbook. I am not sure what I am doing wrong but I attached the file that I am working in case you would like to have look.

Also, it closes ALL open workbooks and not just the specific workbook I would like to AutoClose. Any ideas?

Please note that I had already posted a question relating to this workbook a couple of days ago and did intend to re-post! Instead, I wanted to address this issue separately and incorporate the answer into my AutoClose workbook so I could learn the process.

The timer on the file is set to 20 seconds which will be too quick to do anything so I also included the codes within the .zip file.

Thanks,

Nick