Change Event not working

Cells in Column M have a data validation drop-down list. If the user selects 'Closed', I want this code to run:

Please Login or Register  to view this content.

This code works fine run on its own. But as soon as I run it from the change event (code below) it falls over.

Please Login or Register  to view this content.

I get a Type Mismatch on If Target.Value = "Closed" Then. Can someone explain why this is happening?

-Militia

Hi, It seems to me that because it is a "Change Event" and the Target Has to equal "Closed". You need to enter the word "Closed " In your Column "M" Range. Then it works for me, else you could use The "Selection Change Event".
NB:- I did get your "Error" when I tried Dragging some Cells in you Range, But that can be overcome. (If Target.count = 1 then)
Regards Mick

RoyUK - Firstly, thanks for the improvement to my code. One problem though, when run I get message "Application defined or object defined error" on line .Range(Cells(LastRow, 4), .Cells(LastRow, 14)).Validation.Delete. Why might this be?

I've been putting my change event in the sheet where the change will be made. Is that where it should be?

MickG - I'm very new to writing code, until now i've begged and stole code. I can't find much about on SelectionChange Event. I tried just changing the first line without success.

Thats the exact setup i've got.

I've found something very strange. Where I said your code is falling over, I stepped through it to see if i get any clues. What I found was, If my screen is displaying the "Closed" sheet, it works as it should. But if it is displaying the "Open" sheet (where the data is originally), which it would do at the moment, it falls over.

I've now added in a line and it works fine, ever come across that before?

Please Login or Register  to view this content.

Despite this, i still have a problem in getting my change event up and running. I know it can be done as I've found several cases of where people have a drop down and each item, when selected, calls a sub routine. But i've found nothing quite like this, do you think it is possible?

I've attached the work book. You will notice i've commented out the Change Event code as it causes your code to fall over if it isn't.

Many thanks

Good morning RoyUk

After you made your changes the code no longer falls over on that line. Despite this, it still istn't working. I now get "Compile Error: Can't find project or library" and it highlights the word "Date" on this line .Cells(LastRow + 1, 14).Value = Format(Date, "m/d/yyyy")

I know I haven't coded to remove that blank line, but I will do. This is the first project i've done like this and I sure have learnt a lot - a bit of planning would have gone a long way.

Cheers
-Militia

Originally Posted by shg

In the VBE, do Tools > References, and look at the ticked items (they are all at the top). Look for one that says MISSING and untick it.

Thanks for your input, though I don't a Reference ticked called MISSING.