Trapping selection change on all files

Selection_change can be trapped from the Thisworkbook module for all worksheets of that workbook by using the Workbook_SheetSelectionChange event handler.

Can this be done for any file opened without adding code to the file?

I would like to trap the event when the cell AA12 is selected for ANY file opened on my computer.
LVL 43
Saqib Husain, SyedEngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
HI,

pls try

insert this code in thisWorkbook module

Private WithEvents App As Application

Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Not Application.Intersect(Target, Range("AA12")) Is Nothing Then
         MsgBox "Found"
    Else
    
    Exit Sub
    End If
End Sub

Private Sub Workbook_Open()
    Set App = Application
End Sub

Open in new window

Regards
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
[ fanpages ]IT Services ConsultantCommented:
Hi,

I would advise using a "Personal" workbook saved as an MS-Excel Add-In.

The following code is taken from the Workbook code module ("wbkQ_28241753") of the attached workbook:

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)

  On Error Resume Next
  
  Set clsQ_28241753 = Nothing
  
End Sub

Open in new window


This code is taken from the Public code module "basQ_28241753":

Option Explicit

Public clsQ_28241753                                    As clsQ_28241753
Public Sub Auto_Open()

  On Error Resume Next
  
   Set clsQ_28241753 = New clsQ_28241753
   
End Sub

Open in new window


Finally, this code is taken from the Class module "clsQ_28241753":

Option Explicit
' --------------------------------------------------------------------------------------------------------------
' [ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28241753.html ]
'
' Question Channel: Experts Exchange > Software > Office / Productivity > Office Suites > MS Office > MS Excel
'
' ID:               28241753
' Question Title:   Trapping selection change on all files
' Question Asker:   ssaqibh                                    [ http://www.experts-exchange.com/M_955320.html ]
' Question Dated:   2013-09-17 at 15:06:12
'
' Expert Comment:   fanpages                                   [ http://www.experts-exchange.com/M_258171.html ]
' Copyright:        (c) 2013 Clearlogic Concepts (UK) Limited                           [ http://NigelLee.info ]
' --------------------------------------------------------------------------------------------------------------

Public WithEvents App                                   As Excel.Application
Private Sub Class_Initialize()
    
 On Error Resume Next
 
  Set App = Application

End Sub
Private Sub Class_Terminate()

  On Error Resume Next
  
  Set App = Nothing
  
End Sub
Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

  On Error Resume Next
  
  If Not (Intersect(Target, Sh.[AA12]) Is Nothing) Then
     MsgBox "Cell [AA12] has been selected in Worksheet [" & Sh.Name & "].", _
            vbExclamation Or vbOKOnly, _
            Sh.Parent.Name
  End If ' If Not (Intersect(Target, Sh.[AA12]) Is Nothing) Then
  
End Sub

Open in new window


Please download the attachment & place it within an XLSTART folder, or in a folder designated within your MS-Excel application's options/settings where files will be stored to be opened whenever MS-Excel is opened ("At startup, open all files in:...").

Then, open MS-Excel, & any other workbook(s) you wish to use as a test.

Select any worksheet in the workbook(s), & then select cell [AA12].

A messagebox will be displayed indicating cell [AA12] has been selected.  The respective worksheet name will also be displayed.  The title of the messagebox will indicate the name of the respective workbook where the worksheet resides.

BFN,

fp.
Q-28241753.xla
0
Saqib Husain, SyedEngineerAuthor Commented:
This was simple and serves my purpose. Thanks
0
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

[ fanpages ]IT Services ConsultantCommented:
Can this be done for any file opened without adding code to the file?

Where did you add the code that Rgonzo1971 posted?
0
Saqib Husain, SyedEngineerAuthor Commented:
To the wb module of a file which would be always open.
0
Saqib Husain, SyedEngineerAuthor Commented:
0
[ fanpages ]IT Services ConsultantCommented:
Can this be done for any file opened without adding code to the file?
Where did you add the code that Rgonzo1971 posted?
To the wb module of a file which would be always open.

If only you had mentioned you always had a specific file open, & open at all times you wished to use any other workbook, in your opening text in this thread!

Maybe Rgonzo1971 was already aware of this fact from a previous question.

Either way, did you try my suggestion?
0
Saqib Husain, SyedEngineerAuthor Commented:
If only you had mentioned you always had a specific file open,
I apologize for that. I did not think that way when asking the question. It simply happened so.

Maybe Rgonzo1971 was already aware of this fact from a previous question.
Definitely not; as this is a new thing.

Talking about "previous question" this is actually a continuation of the timesheets I was working on where each digit of the totals was in a separate cell. The news is that I managed to change it and now I have a normal total cell.

Either way, did you try my suggestion?
No. I was too scared to and also because the simple one did work fine. But I know that this is available in the kitty and would definitely acknowledge if ever I happened to use it. Thanks for you effort and sorry about me not being able to use it.
0
[ fanpages ]IT Services ConsultantCommented:
OK, thanks for your comprehensive reply.

Well done with changing the Timesheet layout! :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.