VBA to run Macro based on Change + New Tab

Lewis Walters

New Member
Joined
Jul 8, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Hi all,

So I'm going to be honest. I know very little about writing VBA.

I'm working on a sheet with 2 tabs.

On the first tab I want to be able to fill out a table with Y's and N's which defines which information shows in Tab 2.

With a bit of Googling I've written a Macro to do this, however I want it to automatically execute and that's where the trouble comes.

Ideally I'd like for the Macro to run when I click on Tab 2 ONLY if something in Tab 1 has changed.

I understand I can use the Worksheet.Change Event to run the Macro but this is irritating as every time I change anything in the summary list from Y to N then it updates.

I also understand that instead I can use the Worksheet.Activate Event to run the macro every time I click on Tab 2, but this gets annoying if I'm flicking between tabs but haven't updated anything on Tab 1.

How would I go about combing the two? Or am I barking up the wrong tree and approaching this in the wrong way?

Thanks,
Lew
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,507
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

I understand I can use the Worksheet.Change Event to run the Macro but this is irritating as every time I change anything in the summary list from Y to N then it updates.
I am a little confused, because isn't this precisely what you want to happen?
How is it irritating?

Is the issue that you want to make all your data changes first, then move the data when you are finished?

If so, then maybe have a Worksheet_Change event procedure that updates a "flag" on the record, indicating that it needs to be moved. It wouldn't move anything, just update a column on that row.
Then, have a second procedure (maybe a "Worksheet_Activate" procedure or a procedure linked to a Command Button you click) that then moves those "flagged" records, and clears the flags for next time.
 

Lewis Walters

New Member
Joined
Jul 8, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
I'll admit, I probably explained it badly.

On Tab 1 I have a list of say 30 items that can be toggled to Y or N. The value of the Y's and N's on Tab 1 dictates which rows are hidden on Tab 2.

Using the Worksheet_Change means that if someone were to work their way down the list on Tab 1 changing certain items to N, every time they make the change, the Macro runs. The problem is that the Macro takes a few seconds to complete which becomes frustrating. Especially when what's being affected is on another tab that you can't see anyway.

So what I want is for the Macro to only run as you click onto Tab 2 as this is the only time it matters. BUT, only if something has changed on Tab 1 otherwise it equally becomes annoying if you're flicking from tab to tab and the Macro is running an update each time.

Any clearer?

Thanks a lot for your time.

Thanks,
Lew
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,507
Office Version
  1. 365
Platform
  1. Windows
Using the Worksheet_Change means that if someone were to work their way down the list on Tab 1 changing certain items to N, every time they make the change, the Macro runs. The problem is that the Macro takes a few seconds to complete which becomes frustrating. Especially when what's being affected is on another tab that you can't see anyway.
It probably shouldn't take that long to run. There may be ways to improve your code so it runs faster. If you post the code, we can help you try to improve it.

So what I want is for the Macro to only run as you click onto Tab 2 as this is the only time it matters. BUT, only if something has changed on Tab 1 otherwise it equally becomes annoying if you're flicking from tab to tab and the Macro is running an update each time.
Did you consider what I said in my last paragraph? I think adopting one of those approaches would do what you want.
 

Forum statistics

Threads
1,144,523
Messages
5,724,836
Members
422,582
Latest member
Vaibhav Wadhwa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top