CODEWITHSUNDEEP
excelvba
BruceWayne
BruceWayne

Reputation: 23283

Run macro when on Worksheet change (i.e. changing sheets, not data within sheet)

I have a workbook with 20+ worksheets. Each worksheet has data I'm inputting, separated by tons of columns. There are three categories I need to fill in, e.g. "Name", "Date", and "Comments". Instead of having to scroll right/left on each sheet to find "Name", "date", etc. I am thinking to instead create a named range for these three categories, when that sheet is selected.

I do not need to trigger this NamedRange macro when a cell changes on a worksheet, rather I need it to run when the worksheet itself changes. (So, I don't think Private Sub Worksheet_Change() is what I want, I don't need to monitor each cell within the worksheet. Just want to know if the sheet itself changes.)

The only other option is to add the code to each worksheet, using Worksheet_Activate. However, I'd have to go in and add code to 20+ sheets, is that the only way? I'm sure I'm forgetting 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

So the idea is that when I activate a new sheet, it clears the current "Name"/"Date"/"Comment" named range, resets it to refer to the active sheet's addresses. This way I can just quickly use the formula bar dropdown to go to the named range.

Any ideas other than putting the above code in each worksheet? I tried putting it in ThisWorkbook but it doesn't do anything. What am I not considering?

Upvotes: 1

Views: 746

Answers (2)

ΑΓΡΙΑ ΠΕΣΤΡΟΦΑ
ΑΓΡΙΑ ΠΕΣΤΡΟΦΑ

Reputation: 1672

'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

Upvotes: 0

Warcupine
Warcupine

Reputation: 4640

Use Workbook_SheetActivate(ByVal Sh as object)

See here: https://learn.microsoft.com/en-us/office/vba/api/excel.workbook.sheetactivate

Upvotes: 2

Related Questions