making certain cells obligatory/madatory in a row

[talochka]

Hi,
I was looking for a solution for days but cannot find exactly what I'm looking for.

I have a file (log for received calls in a call center) where each row represents a call. Once the call is completed, agent is supposed to fill out almost every cell in a row and then put "completed" in the column Q. I want to make certain cells mandatory. And I want a code that verifies if those mandatory cells are not empty in the row once it has been put "completed". If they are empty - user gets a message and cannot continue.
The cells that are supposed to be mandatory are E, F, K, and N.

Also (that's an extra), I would like the code (after the row is marked "completed") to check if cell E in a row is "annulé" and if it is - automatically put "annulé" in all cells in the row (from C to P).


Please, Excel gurus, I really need your help....

[jaslake]

Hi talochka

This Code is not tested as you've not posted a sample file. Place the Code in the Appropriate Sheet Module...it'll be triggered by a change in Column Q.

Please Login or Register  to view this content.

[talochka]

Hi jaslake,

The code is working perfectly with making columns mandatory. But for the "annulé" part - it doesn't fill out the rest of the columns with "annulé". Could it be because cells have a data validation rule (they all have the option "annulé" though)?

Here is my original file, without your code. It already has some code that protects the row once the call is completed. I do not know VBA very well and I cannot integrate your code so that it doesn't interfere with the one I've already have.

Your help is greatly appreciated!!

[jaslake]

Hi talochka

Try the attached...

[talochka]

Hi John,
sorry, I didn't attach the right file - here is the file with my existing code.

p.s. I've tried your sample and it doesn't put "annulé" in all cells once I choose "annulé" in cell E and then "completed" in cell Q.

[jaslake]

Hi talochka

If I understood what your original Change Event Code was doing the Code in the attached appears to do as you require.

mandatory are E, F, K, and N

check if cell E in a row is "annulé" and if it is - automatically put "annulé" in all cells in the row (from C to P).

Please Login or Register  to view this content.

[talochka]

Hi John,

It does work perfectly except for the "annulé" part. I've opened the file and when I choose "annulé" in the column E and then "complété" in column Q - I get a message "Missing data". So it doesn't automatically fill out the whole row with "annulé". If it is too complicated I would be happy with another solution - for example if you choose "annulé" in column E then you don't have to fill out the rest and can simply put status "complété".

Your help is greatly appreciated!!

[jaslake]

Hi talochka

The Code is not designed to

automatically fill out the whole row with "annulé".

if "annulé" is entered in Column E. It's designed to do so AFTER the fact. Sorry, I misunderstood.

I'll revise the Code to do so. However, what if the user selects "annulé" in Column E by mistake and wants to make a change? What should happen? Should the other Columns be cleared?

[jaslake]

Hi talochka

With this Code, if the user enters "annulé" in Column E, Cells C through P are also filled with "annulé" and Cell Q is filled with "complété". You'll need to define for me what happens if the User changes their mind

what if the user selects "annulé" in Column E by mistake and wants to make a change? What should happen?

Please Login or Register  to view this content.

[talochka]

It works perfectly!!!
As for the scenario where the user changes his mind - well, too bad for him, his mistake - he'll have to correct it manually

Thank you for you help!!! I really appreciate it!!

[jaslake]

You're welcome...glad I could help. Thanks for the Rep.