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

--
Gordon Smith (eMVP)

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)" <[email protected]> wrote in message
news:OLS31V#[email protected]...
> (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.
>
> --
> Gordon Smith (eMVP)
>
>

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)

The customer is always right but not always correct!

Regards,
Peter T

"Gordon Smith (eMVP)" <[email protected]> wrote in message
news:[email protected]...
> 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)
>
>

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)" <[email protected]> wrote in message
> news:[email protected]...
>> 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)

"Gordon Smith (eMVP)" <[email protected]> wrote in message
news:OLS31V%[email protected]...
> (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

"Gordon Smith (eMVP)" <[email protected]> wrote in message
news:OLS31V%[email protected]...
> (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