Data validation Change event

[SathishKumar]

hi,

i have listbox created through data validation.
i need a macro to run when a change in the validated listbox occurs.

note:we cant do this with worksheet change event

can anuone help me on this??

[HaHoBe]

Hi, SathishKumar,

note:we cant do this with worksheet change event

The only other way I know is to link the cell to another one, use something like NOW() in another cell and use the Worksheet_Calculate. But my favourite would always be the Worksheet-Change. But even better would be not to use Data/Validation but a ComboBox instead and attach the macro to that (I would prefer the one from the Toolbox for the events available).

Ciao,
Holger

[SathishKumar]

Caio,
I dint get you..
Can you pls elaborate on this??

Thanks.

[HaHoBe]

Hi, SathishKumar,

for example your Data/Validation is in B2, put a formula in B3 referencing that cell:

Please Login or Register  to view this content.

If the Calculate-Event wonīt get fired on that enter another formula in B4

Please Login or Register  to view this content.

If calculation is set to automatic this will fire on any change of a formula in a cell.

Iīm really interested of why you state that the Worksheet_Change-Event canīt be used - if any other procedure is started from that code for Data/Validation can be implemented there.

Ciao,
Holger

[SathishKumar]

caio,
i'm giving u the full requirement
i have attached the excel sheet.
In that, column 1 has a data validation list.
when i change the list value to h, and if it has empty value in the next 3 columns.
then it should give me a message.
i need this when a new change occurs in the validation list.
could you help me in this..
thanks...

[nilem]

maybe so

Please Login or Register  to view this content.

[SathishKumar]

nilem,
you r so great..
this is the apt one i needed.
Thanks for this...
but can you explain this...As i dont have deep knowledge in this..Can you/?

thanks a lot.

[nilem]

Ok, I'll try to comment on your words

Please Login or Register  to view this content.

[SathishKumar]

Thank you so much..
its hard to understand for me.coz i'm a beginner.
but i'll try to learn this..
i'll make this as solved.
thanks again.

[SathishKumar]

@nilem,

Below Qns for u::
For the above condition,
i need to update the color of the cell to blue if the next three values were empty.. i tried this but getting an error..
And one more silly doubt.. In the above code why endif is not provided for IF condition??

if you have any materials regarding this pls share with me..
Thanks.

[nilem]

Hi Satzz. "End If" not required for single-line If...Then, for example

Please Login or Register  to view this content.

try it

Please Login or Register  to view this content.

You can also use conditional formatting (see Sheet2 in attached file)