Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-17-2014, 12:39 AM
tinfanide tinfanide is offline How to Control Worksheet Event Handler in Module? Windows 7 64bit How to Control Worksheet Event Handler in Module? Office 2010 32bit
Expert
How to Control Worksheet Event Handler in Module?
 
Join Date: Aug 2011
Posts: 310
tinfanide is on a distinguished road
Default How to Control Worksheet Event Handler in Module?



Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub
For example, the above codes must be put under a sheet to be effective. Is it possible to make it effective under a module? Because I want to use the codes contained in a module every time for any other Excel files.

Any help is appreciated.
__________________
Scripting a day keeps the doctor away.
Reply With Quote
  #2  
Old 10-17-2014, 09:51 PM
macropod's Avatar
macropod macropod is offline How to Control Worksheet Event Handler in Module? Windows 7 64bit How to Control Worksheet Event Handler in Module? Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,433
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

As you've already observed, the Worksheet_SelectionChange code has to be run at the sheet level. Depending on your objective, you may be able to use the Workbook_SheetChange event, which runs from the 'ThisWorkbook' module and applies to all sheets.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 10-19-2014, 09:46 AM
CoolBlue's Avatar
CoolBlue CoolBlue is offline How to Control Worksheet Event Handler in Module? Windows 7 64bit How to Control Worksheet Event Handler in Module? Office 2013
Advanced Beginner
 
Join Date: Jun 2014
Location: Australia
Posts: 40
CoolBlue is on a distinguished road
Default How to Control Worksheet Event Handler in Module?

You could create a class Module to wrap the worksheet.
By way of a very basic example...

In a Class Module called clWsEvents
Code:
Dim WithEvents Ws as Worksheet

Private Sub Ws_SelectionChange(ByVal Target As Range)
    'Do stuff
End Sub
In a Standard Module
Code:
Dim gWs as clWsEvents

Sub Main()
    Set gWs = New clWsEvents
     Set gWs.Ws = ActiveWorkbook.Sheets('sheetName')
    'Do other stuff
End Sub
There are many possible refinements and I may not be letter-perfect with the syntax, but this is the basic idea. Feel free to google Vba Class Modules and WithEvents to inform yourself about it.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Inserting a Word Module into excel SarahBear Excel Programming 12 06-11-2014 05:26 AM
How to Control Worksheet Event Handler in Module? How to summarise different worksheet to a summary worksheet samkiewhock Excel 1 09-06-2012 03:34 AM
AZWizard Module - ?hidden module pcaldwell Word 1 08-22-2012 01:19 PM
How to Control Worksheet Event Handler in Module? "Auto-populating" data-worksheet to worksheet. meggenm Excel 4 02-04-2012 02:04 AM
Outlook 2002 crashing - faulting module msmapi32.dll Ken Cuvelier Outlook 3 12-12-2005 03:55 PM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 10:57 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft