VBA to update all changes to same item?

[mrbusto71]

Hey folks,

I thought this would be easy, and maybe it is, but for the life of me I can't figure out how to approach this. VBA might not even be needed, but I'm totally open to incorporating it into my existing code.

The context is this:
I have a workbook with multiple sheets. The same information can be present on multiple sheets because it's a classroom tracker for the actual classes. So the title "Class Name #1" could show up on a few sheets.

There are about 900 classes in total. My issue is, the class names change pretty regularly, and new classes with new names are added daily. Currently, if a class name changes, I have to go find it in each tab, then replace the old name with the new one. It's a pain, and if I forget to do the update on a sheet it breaks everything.

So my question is: What's the easiest way to make it so that when I update a class name in one location, it updates everywhere? I thought of doing a Data Validation list, however that doesn't update old entries on other sheets (in otherwords, if I create a Master List tab with all the class names on it and have that be my data validation list, it works except for areas where the old name was already present and I'd have to go re-select those to update).

Again, I feel like this is probably easy but after my data validation idea failed, I've suddenly lost the ability to problem solve...

Help? Please?

[sintek]

This as an example...
You might have some sheets you want to ignore...Just amend code to accommodate

Please Login or Register  to view this content.

[mrbusto71]

This as an example...
You might have some sheets you want to ignore...Just amend code to accommodate

Please Login or Register  to view this content.

Oh man, this works brilliantly!

Silly question, but how would I modify this code so that on a button click it'll prompt me to type in the Old Name (Class Name #1) and the new name (Class Name #2)? This will be user facing, so setting it up so they can change on a button click will prevent confusion for editing code in the code viewer.

I know how to do a VB Yes/No box, but Google is suggesting I use a User Form? But that feels excessive (but maybe necessary!). Thoughts?

Thanks again, rep added regardless!

[sintek]

I like User Forms...They can be manipulated formatted designed etc...Input msgBox are limited...