After select one value from list jump into specific cell for mandatory input

[pete1980]

Hello all, hope you can help me out here a little bit. I'm trying to accomplish the following thing.

I have a column D with a drop down list configured by data validation. The values from the list are coming in this example from column H. There are few values the user can pick from. One of these values is 'Released'. When the user selected 'Released' for example in cell D2 I want him to jump in cell E2 and force the user to enter a value before leaving this cell. This behavior would be required for the following cells as well like for example selecting 'Released' in cell D3 then jump right away in cell E3. If the user is picking another value from the list beside 'Released' like 'Open' there is no need to jump in the cell of column E. Hope I could explain my challenge here. I'm using Excel 2016.

Thank you


000016.jpg

[dangelor]

Not very practical but possibly...

Please Login or Register  to view this content.

...placed in the worksheet's code module.

[pete1980]

Hi dangelor, thanks for the support.
But nothing happened when I pasted the code into the worksheet code.
And how do I know that it's column D and E where the code is taking affect?

Thank you

[dangelor]

The code searches column 4 ("D") for a cell value of "Released". If it find one, it's 'labeled' "c". It then looks at the cell one column to the right ("E") of c and if that cell is empty, it selects that cell. If it isn't empty, it looks for the next cell in column 4 that meets the requirements.

My guess is that your file has events disabled. Try running this line of code in the Immediate window (Ctrl + G)

Please Login or Register  to view this content.

Meanwhile, it works in the file below.

[pete1980]

yes that's exactly what i'm looking for...AWESOME! but for some reason it doesn't run in my excel...
i'm getting an error. Attachment 677721

[pete1980]

...and now your excel won't work either. Am I missing something? Needs the macro to be activated somehow? Thanks for your help

[dangelor]

Your link doesn't work. Try the Application.EnableEvents code again. Meanwhile I'll try some error trapping. May not be until tomorrow, though.

[pete1980]

It actually works when i'm using the code Application.EnableEvents = True again. You have an idea how it will stay permanent?

Thank for your help

[pete1980]

sorry disregard this post

[dangelor]

It seems that when macro errors after events are disabled, excel doesn't automatically re-enable them like it does with screen updating. As a 'fix', I've added a simple error trapping routine, but if events are disabled in another macro, it disables events throughout the entire application (any and all opened workbooks during the duration of the application) until they are again enabled. I also posted two simple routines the enable and disable some application setting to help speed up subroutines. I keep them in my personal.xlsb workbook and add them to the quick access toolbar. The Enable macro will enable the events.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[pete1980]

I will definitely check your code.
Additional question. Is it possible to add also an message like the pop up warning that the user is aware that he needs to enter the value first in the cell where is routed to?

Thank you so much.

[dangelor]

Try this

Please Login or Register  to view this content.

[pete1980]

Awesome you are the man!
Thank you so much for the support! Really appreciate your help.

[dangelor]

Glad to help!

[pete1980]

Hi dangelor,
hope you can help one more time.
I'm getting actually following error when i'm starting my list from scratch. I deleted all content to prepare that list for using. It looks like the list needs to have at least one 'Released' value. But it might take few days before that happen and we still want to use that list.
The error disappear once i'm selecting the value 'Released' at least once.

Greetz

Attachment 679558