2nd Selection Change Event occurance

[jomili]

I'm seeing some strange behaviour, and don't know the reason. My code below is supposed to add a date and rename the sheet when D8 is changed (it's changed via a dropdown list). What's happening is that when the dropdown list is chosen, it autofills but nothing else happens. However, if I then go back, then or later, and change the selection or delete it, then the change event happens. Can someone tell me why it's not happening on the first change event?

Please Login or Register  to view this content.

[TMS]

You probably need Change rather than SelectionChange.

And you need to disable and re-enable event handling if you make changes to the sheet based on a monitored change.

Regards

[jomili]

TM,

The Change vs SelectionChange did the trick. However, I didn't understand where in my code I should be disabling and re-enabling events. I didn't see anything that would require it. Will you please advise?

[TMS]

Please Login or Register  to view this content.

Regards

[Colin Legg]

Jomili,

You should include some errorhandling to ensure that Application.EnableEvents is always set back to true. If it isn't then your change event handler won't be called again. Here's a basic example:

Please Login or Register  to view this content.

[snb]

I'd prefer

Please Login or Register  to view this content.

[Colin Legg]

That's basically what I posted but, as noted, if the target is D8 then that would be called twice, not once. So it's not an alternative that I would prefer, at all.

[snb]

Nothing wrong with being called twice, because of the builtin check for the target address.
That would be another matter if the first target would be changed by the code.

[Colin Legg]

Nothing wrong with being called twice,

Of course it matters: it's inefficient!!! Why have it called twice when it is only necessary to call it once?


Also, when you condensed the code you decided to omit the IsEmpty() check which the OP included in the original code. This, means that your code will update the sheet name and cell B2 when D8 has been cleared; so the functionality of your code is most probably not what the OP wants.

[jomili]

As the OP here, here's what I want:

IF D8 is changed (once) I'd like today's date in B2, and the name of the sheet tab changed to "Expdtl" and today's date. If the D8 is changed again, no action is necessary, nor is it desired, but if it happens it's not the end of the world.

So, I have 3 different sets of code to run with, and no idea now which is better for my purposes. Help?

[TMS]

One way, a fourth option, my way:

Please Login or Register  to view this content.

Or something along those lines

Regards

[TMS]

Or:

Please Login or Register  to view this content.

Regards

[jomili]

What does this line do?

Please Login or Register  to view this content.

EDIT: I just saw your "Or" post. Now I understand. You're checking to see if the sheet is already named "ExpDtl...", and if so you exit the sub. Nice!

[TMS]

Or, a bit shorter:

Please Login or Register  to view this content.

[TMS]

The last (?) version is better because a) it specifically refers to the sheet name you use and b) it's a lot shorter.

If you did get through and try to rename the sheet again, at least if it's on the same day, it would fail.

Regards

[jomili]

Your last, after changing "exdtl" to "expdtl", will do the trick quite nicely. Thank you for your help on this one. Colin and SNB, thank you so much for your input.

Thanks,
John

[TMS]

It was meant to say "expdtl" ... ho, hum. I'm sure it did when I tested it. Note that it did say "expdtl" in version 2 ;-)

Anyway, you're welcome.

If this has answered your question, please mark your thread as solved. See my signature for details or the FAQ.

Regards, TMS