CODEWITHSUNDEEP
vbaexcel
Mithun Manohar
Mithun Manohar

Reputation: 586

Accessing worksheet methods from module

Here is what I am trying to achieve. I want the user be shown a message that a specific worksheet has been changed and ask him to run a macro on that sheet manually. I can use the worksheet_change method like this 

Private Sub OptionButton1_Click()

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "Hey! Cell " & Target.Address(0, 0) & " just changed!", 
End Sub

Above code has to be in the worksheet module. I would like to know if there is a way to do this from my custom module, the reason being I have thousands of excel workbooks that need to be updated and I can easily replace the old module with updated one with another macro which I usually use to update the code in workbooks.

Upvotes: 1

Views: 163

Answers (2)

dtk142
dtk142

Reputation: 51

You can create an add-ins file as application-level event handler for this case https://support.microsoft.com/en-us/kb/213566

The advantage of this way: - can use for every workbook that you open without copy any code again

Upvotes: 0

R3uK
R3uK

Reputation: 14547

In your custom module, place this :

Public Sub My_Code_for_Changes(Target As Range)
    MsgBox "Hey! Cell " & Target.Address(0, 0) & " just changed!"
End Sub

In your sheets' modules, you'll only have to place this :

Private Sub Worksheet_Change(ByVal Target As Range)
    My_Code_for_Changes Target
End Sub

Or to handle all the sheets of the workbook (you can exclude some) :

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = "NameToExclude" Then Exit Sub
    My_Code_for_Changes Target, Sh
End Sub

With this :

Public Sub My_Code_for_Changes(Target As Range, Sh As Worksheet)
    MsgBox "Hey! Cell " & Target.Address(0, 0) & " in sheet " & Sh.Name & " just changed!"
End Sub

Upvotes: 3

Related Questions