Run macro when on Worksheet change i e changing sheets not data within sheet

Question

In my workbook, there are over 20 worksheets. I'm entering data into each worksheet, separating it with numerous columns. I have three fields to complete, such as "Name," "Date," and "Comments." Instead of having to scroll right or left on each sheet to find "Name," "date," etc., I'm considering making these three categories part of a named range that appears when a certain page is selected.

This NamedRange macro should execute whenever the worksheet changes rather than every time a cell on a worksheet changes. I don't need to keep track of each spreadsheet cell, therefore I don't think Private Sub Worksheet Change() is what I want. Just curious as to whether the sheet itself alters.)

The only other choice is to use Worksheet Activate to add the code to each worksheet. But is it the only option as I'd have to add code to more than 20 sheets? I'm certain I've forgotten something.

The macro would be:

Private Sub Worksheet_Activate()
Dim ws as worksheet
Set ws = ActiveSheet
On Error Resume Next
' Delete the named ranges that exist, so you can reset
For Each nm In ActiveWorkbook.Names 
    if nm = "Name" or nm = "Date" or nm = "Comment" Then nm.Delete 
Next nm
On Error Goto 0
ws.names.add Name:="Date", RefersTo:=ws.Range("A1")
ws.names.add Name:="Name", RefersTo:=ws.Range("KK1")
ws.names.add Name:="Comment", RefersTo:=ws.Range("ZZ1")
End Sub

The concept is that the current "Name"/"Date"/"Comment" named range is cleared when I activate a new sheet and is then reset to refer to the addresses of the active sheet. In this manner, I may rapidly access the named range by using the formula bar dropdown.

Are any suggestions other than including the aforementioned code in each worksheet? I tried inserting it in ThisWorkbook, but nothing happened. What am I overlooking?

Answer 1

Try this:

'put this sub in ThisWorkbook module (is workbook's event)

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
   Call onActivate(Sh)
End Sub


'and this in a module
Public Sub onActivate(ws As Worksheet)
   Dim nm As Excel.Name
   On Error Resume Next
   ' Delete the named ranges that exist, so you can reset
   For Each nm In ActiveWorkbook.Names
       If nm = "Name" Or nm = "Date" Or nm = "Comment" Then nm.Delete
   Next nm
   On Error GoTo 0
   ws.Names.Add Name:="Date", RefersTo:=ws.Range("A1")
   ws.Names.Add Name:="Name", RefersTo:=ws.Range("KK1")
   ws.Names.Add Name:="Comment", RefersTo:=ws.Range("ZZ1")
End Sub