Link to home
Start Free TrialLog in
Avatar of Saqib Husain
Saqib HusainFlag for Pakistan

asked on

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.
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of [ fanpages ]
[ fanpages ]

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
Avatar of Saqib Husain

ASKER

This was simple and serves my purpose. Thanks
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.
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?
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.
OK, thanks for your comprehensive reply.

Well done with changing the Timesheet layout! :)