Intercepting multi page change

This may be a 'cant be done' buy thought I would ask the brain-trust ...

I am trying to figure out a way to intercept when the user clicks on the tab of a form's multi-page so that I can test if there has been a change and, if there has, give the user the option to save BEFORE changing to the next tab. While I can intercept changes to the data, whatever I do I can't get the VbMsgBox to pop up before the page actually changes.

I tried:

Please Login or Register  to view this content.

but it doesn't reset - presumably because by the time this runs, I am in effect, resetting the new multipage back to the new multipage.

I am not entirely sure what the syntax is for holding a variable for the 'current' page and swapping back to it if the user wants to cancel but I'm pretty confident I can do that as a plan B.

Effectively there are 3 options:

1. User wants to save changes => run sub to save the data from tab or whole form
2. User does not want to save changes => presumably I just don't run sub that transfers 'new' value to worksheet and reset any changed value on the multipage to value on worksheet
3. User wants to cancel change => swap back to original multipage from new multipage

My question really boils down to, can I ask the question (the VbMsgBox) and process the choice before the multipage changes from original to new? It looks like the act of clicking triggers the change multipage event before anything else gets to chime in. Is it possible to stop this or an easy way to bounce back to the original so, from the user perspective, nothing changed?

Any thoughts on direction for me to try would be appreciated

Thanks @ByteMarks, I was getting close to it but the tab refreshes just wouldn't work ... It feels like a bit of a poorly executed feature of Excel.

I will see if I can extend your code to the main Workbook im trying to get to work.

Very much appreciate the assist.

@romperstomper Thanks for the note. I get where you are coming from ... I was trying to keep the interface simple, which was fine before I started incorporating logic to check for changes before swapping. I might rill with an alternate next time.

Thanks

Thanks @torachan, that's an interesting approach ... I think I follow the logic, I guess the code for managing the 'change' would be easier to intercept within the button event itself and it would only show the tab when the event (including all queries/validation etc) was complete.

I am getting past the multipage - I like Bytemarks solution but Multipage generally just seems a flawed feature within Excel and I get the feeling it will break in a new and unusual way later.

I will see if i can do a proof of concept within my Workbook.

Really appreciate the demo

Thanks for the additional @torachan, I like the feature of the multipage generally but it seems a missed opportunity that you cant intercept the page change within the change page event. I also found it odd that when I forced a change back to the previous page, the tab changed but the page controls didn't. It just seems it could work in a more procedural fashion 'out of the box', expecting users to need more control over the tab changes (accepting your more advanced way of working around it).

I will look at your additional examples and see if I can get my head around them

Thanks