Controlling switching to other sheets
 

(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 a
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.

--
Gordon Smith (eMVP)

Controlling switching to other sheets
 

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)" wrote in message
...
(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 a
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.

--
Gordon Smith (eMVP)

Controlling switching to other sheets
 

Peter T wrote:
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.

--
Gordon Smith (eMVP)

Controlling switching to other sheets
 

The customer is always right but not always correct!

Regards,
Peter T

"Gordon Smith (eMVP)" wrote in message
...
Peter T wrote:
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.

--
Gordon Smith (eMVP)

Controlling switching to other sheets
 

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

Peter T wrote:
The customer is always right but not always correct!

Regards,
Peter T

"Gordon Smith (eMVP)" wrote in message
...
Peter T wrote:
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.

--
Gordon Smith (eMVP)

--
Gordon Smith (eMVP)

Controlling switching to other sheets
 

"Gordon Smith (eMVP)" wrote in message
...
(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 a
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