Controlling switching to other sheets

[Gordon Smith \(eMVP\)]

(Please ignore the MVP part of my "name". I'm not an Excel MVP and the
question I have is probably fairly basic.)

I have a XLS which is really an order form. I want to prevent navigation to
other sheets until all "mandatory" fields are filled out on the current
sheet. With a quick search on google, I found 2 approaches. I was
wondering if there is a 3rd which maps better to what I want to acheive.

The 2 I found are:
1. Keep the other sheets hidden until all of the mandatory fields are
filled out.
2. Use the Workbook_SheetActivate event to pop up a warning dialog and then
move them back to the original sheet. This looks unprofessional in that you
get a glimpse at the next sheet while the dialog is up. Once you click
"ok", you are brought back to the original sheet.

What I'm aiming for is:
3. As soon as they click on a tab for a different sheet, pop up the warning
box but WITHOUT actually reaching the next sheet - even if just for a short
time. Is there a "before sheet activate" event or some other similar
approach I should be using?

Thanks in advance.

 

[Peter T]

Hi Gordon,

What you need is a 'before sheet deactivate' event with a Cancel argument,
but there ain't one! Also the deactivate event is triggered after another
sheet has been activated.

What's wrong with the hide/unhide sheets approach.

Regards,
Peter T

 

[Gordon Smith \(eMVP\)]

What's wrong with the hide/unhide sheets approach.

Regards,
Peter T

Nothing. It's what I'd prefer and I suggested it to the customer but...
the customer is always right.

 

[Peter T]

The customer is always right but not always correct!

Regards,
Peter T

 

[Gordon Smith \(eMVP\)]

This particular customer is very reasonable. We'll see if he wants "hide
sheets until valid" or "flash the wrong sheet real quick then bounce back"
approach.

Thanks,
Gordon

 

[Joerg]

(Please ignore the MVP part of my "name". I'm not an Excel MVP and the
question I have is probably fairly basic.)

I have a XLS which is really an order form. I want to prevent navigation to
other sheets until all "mandatory" fields are filled out on the current
sheet. With a quick search on google, I found 2 approaches. I was
wondering if there is a 3rd which maps better to what I want to acheive.

The 2 I found are:
1. Keep the other sheets hidden until all of the mandatory fields are
filled out.
2. Use the Workbook_SheetActivate event to pop up a warning dialog and then
move them back to the original sheet. This looks unprofessional in that you
get a glimpse at the next sheet while the dialog is up. Once you click
"ok", you are brought back to the original sheet.

[snip]

You can use approach 2: Bring the user back to the original sheet *before*
you pop up your warning. I just tested it and apart from a tiny flicker
(caused by the shortlived new sheet and my old hardware) it looks OK.
Joerg