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

0 votes

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?

Mar 31, 2023 in Others by narikkadan
• 63,600 points
653 views

1 answer to this question.

0 votes

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
answered Mar 31, 2023 by Kithuzzz
• 38,000 points

Related Questions In Others

0 votes
1 answer

In a excel formula I need to create a list of names on one sheet based upon criteria/data of another sheet

The final formula is: =IF(ROWS($H$3:H3)<=$I$1,INDEX(Personnel! ...READ MORE

answered Nov 25, 2022 in Others by narikkadan
• 63,600 points
1,126 views
0 votes
1 answer
0 votes
1 answer

How do I run a VBA Sub routine continuously when working in a Workbook and not only when the Workbook is opened?

on Thisworkbook, put: Private Sub Workbook_Open() Call checkPW(True) End Sub Then ...READ MORE

answered Jan 14, 2023 in Others by narikkadan
• 63,600 points
625 views
+1 vote
0 answers

I am not able to see the chat box on webinar-session. i can see only ask question pane

Today (8/aug/2020) I have joined one webinar, ...READ MORE

Aug 9, 2020 in Others by Ravibharathi
• 130 points
1,468 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
1,246 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,692 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
954 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
1,082 views
0 votes
1 answer

Change sheet tab color depending on CountA function result

Try using the ws. prefix to set ...READ MORE

answered Mar 28, 2023 in Others by Kithuzzz
• 38,000 points
322 views
0 votes
1 answer

Why am I not being able to read the Excel sheet in my Jupyter Notebook

Verify that it is actually in your ...READ MORE

answered Mar 28, 2023 in Others by Kithuzzz
• 38,000 points
699 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP