Updating all validation drop down lists on all worksheets simultaneously

Hi guys

I have a workbook with 20 worksheets. All tabs are identical in format and layout. On B3, I have a drop down list (this may be irrelevant, but it's to select one month of the year) using simple data validation. Now, I initially had a formula in B3 equal to B3 of the first sheet, so that if I changed the month in the first sheet it will change on all sheets, it will do it the first time without touching the drop down lists on the subsequent sheets.

However, if I changed the date on any other sheet other than the first sheet, it will erase my formula and replace with the value I selected from the drop down list.

Can you guys please help me to device a way (code or formula; open to anything) where if I select, say September, on ANY sheet, it will change all the others to that selection (September) as well?

Thank you

Ron

PS: B3 is actually a merged set of cell merging cells B3:D3. I know this is important because code and merged cell don't really mix.

Yep, that's exactly what I want...

Can you please?...

I really appreciate it

Thanks

Ron

Dion, you'd need to be careful with that code or you would end up in a perpetual loop.

In reality it would be better to consider using the Workbook Level Sheet Change Event rather than replicating events on each sheet object.
Given it seems each sheet in the file is to be updated (other than current) this simplifies things somewhat, ie:

Please Login or Register  to view this content.

the above would reside in ThisWorkbook Object in VBE.

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

http://www.mrexcel.com/forum/showthread.php?t=432376

Originally Posted by dominicb

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

http://www.mrexcel.com/forum/showthread.php?t=432376

My bad mate... I am so careful to follow rules and I've missed two rules this month! Just for the purpose of learning....

I'm am really sorry, and I didn't mean to offend anybody or place undue pressure on the helping members of this board or the other's. My apologies, I know now what would be the courteous thing to do from now on.

I don't really do the cross posting often, but to cut the story short is for two reasons:

1. I didn't know that the guru members on this board are member on another board; and maybe it will be 'illegal' to mention another forum as famous as the other one on this board. Now, as I said, I know...

2. Given that I didn't know 1 above, I thought that it would be informational to know how the different gurus would skin this same cat. And at time I do it when after a day or so, I didn't get a satisfactory answer from one board and as a result my post would seem to, more thank likely, be forgotten and downbumped by newer requests.

Thank you very much for all the helping hands though. I really appreciate the help I've had from this forum.

Ron

PS: OH and DonkeyOte, given that probably some will not be able to see that I've said thanks already on another forum, please let thank you here as well. Thank you very much, very much appreciated... Keep up the good work of helping.

Originally Posted by DonkeyOte

Dion, you'd need to be careful with that code or you would end up in a perpetual loop.

In reality it would be better to consider using the Workbook Level Sheet Change Event rather than replicating events on each sheet object.
Given it seems each sheet in the file is to be updated (other than current) this simplifies things somewhat, ie:

Please Login or Register  to view this content.

the above would reside in ThisWorkbook Object in VBE.

Hopefully I am able to post this, after all the trouble I just got into...

But anyways, once again, thank you DonkeyOte, I placed the code above into the ThisWorkbook object and nothing happens when I changed the date on any of the sheets.

Please see attached, I've deleted all data, retained headings, few rows, and 1/2 of the worksheets it usually has, but I don't think that will matter, please advice further...

As always, thanks,

Ron

works for me... I suspect at some point the Events in your environment were disabled and not reset.

In VBE press CTRL + G to bring up the Immediate Window into which type on a new line: Application.EnableEvents = True and press Return.

Now go back to native XL and alter selections and see if it works as you expect ?

Hi DonkeyOte,

I couldn't send you a PM so I had revert to this, hopefully this doesn't disturb you - to post on an already marked "SOLVED" thread. And to all mods, please excuse me but I have no other choice.

Reality is that this thread is indeed solved. It is just a peculiar issue that just applies to my file when opened on my work pc. I opened it at home and it worked like magic! I thought putting this on a separate post, but I, for whatever reason, thought that it will still apply more here than anywhere else.

So this is the issue:
On my work computer it doesn't work because, it has formula in sheet "bze" on L2 (I believe), that uses a udf "pull". So when I opened it at home it doesn't find the Q drive reference, or if I delete it from this file on my work computer and save it, then your code works the way its supposed to.

For whatever reason it doesn't accept the line you suggested to be put in the immediate window. Maybe its because I already have this?:

Please Login or Register  to view this content.

Any more suggestions? Please?

Ron