Adding Worksheet_activate and _deactivate to an existing sheet using

Question

I found Siddharth Rout's great code for adding a new sheet and code for it, but my need is a touch different.

I want to add Activate code to an existing worksheet. I tried substituting that sheets name in place of using the .Name property, dropping the

Set ws = Worksheets.Add

and replacing ws.Name with my existing sheets name, "Work". It bombs at that point.

Inspection of the ws object using the code in the existing example shows the property Name of the ws object to be a simple string, so not being able to use my existing sheet's name there instead is baffling me.

Why:

The existing sheet "Work" is initially created with no Activate code. It is used heavily through many steps in this multisheet multifunction system and I want to prevent the user from changing it if he selects it manually -- there are reasons to let him look at it, to make decisions, during the pauses for his interaction -- yet leave it unprotected otherwise as my VBA bounces to and from it.

So, I want to insert Activate code that protects the sheet -- the Deactivate code that removes the protection will can be permanent part of the sheet, won't hurt -- and so protect the sheet whenever the user decides to look at it manually, whenever my VBA allows user interaction, then remove/alter that code via its Deactivate when the user leaves the sheet and returns to the interactions, so it's normally free for my code's use otherwise without the protection being added each time my code switches to it.

I'll create and leave the Unprotect deactivate code there permanently -- won't hurt -- so it isn't altering itself (which is a horrifying no-no). How?

Sorry for all the words. My hour of google search for "self modifying code" in VBA found no good answers.

Answer 1

If you just want to capture events on the other worksheet temporarily instead you can use a class module. If you want to add code to the worksheets code permanently you'll need to use the VBA Extensibility stuff that @Sorceri mentioned.

To use a class module to capture events of a workbook

In a Class Module (Name: SheetEvent)

Public WithEvents Sheet As Worksheet

Public Sub Capture(ByVal Worksheet As Worksheet)
Set Sheet = Worksheet
End Sub

Private Sub Class_Terminate()
Set Sheet = Nothing
End Sub

Private Sub Sheet_Activate()
Debug.Print "Sheet_Activate Fired! (Sheet:" & Sheet.Name & ")"
End Sub

Private Sub Sheet_Change(ByVal Target As Range)
Debug.Print "Sheet_Activate Fired! (Sheet:" & Sheet.Name & " Target:" & Target.Address & ")"
End Sub

Private Sub Sheet_Deactivate()
Debug.Print "Sheet_Deactivate Fired! (Sheet:" & Sheet.Name & ")"
End Sub

In a module:

Public S As SheetEvent

Sub Main()
Set S = New SheetEvent
S.Capture ThisWorkbook.Worksheets("Sheet1")

End Sub

On issue with this method is that you will need to restart it if your VBA has an error or you stop execution.

Using the above example if you run Main() then switch from Sheet1 to Sheet2 and back, or change any cell in Sheet1 you will get a message in the immediate window.

If by "bouncing" between sheets you mean you are activating the sheet to read (or set some cells) then re-activating the previous sheet, you might find using worksheet objects easier (and quicker).

Dim Sheet1 As Worksheet
Dim Sheet2 As Worksheet

Set Sheet1 = ThisWorkbook.Worksheets("Sheet1")
Set Sheet2=  ThisWorkbook.Worksheets("Sheet2")

With the above code you can access data in both sheet1 and sheet2 via these two objects. No having to activate either first (even if there hidden too)