VBA to run Macro based on Change + New Tab

[Lewis Walters]

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

 

[Joe4]

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]

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]

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.