alternative for (multiple) worksheet change

Hello all, I am rather new to VBA, and am making a spreadsheet in which I originally intended to have multiple worksheet changes. Only to find out I can only have one. My problem is the code I have, and the amount of changes has almost reached the limit for just one data validation cell (I had to trim it already because I was getting a "too large" error. I cannot combine all the changes I need in one worksheet change macro because the size is WAY to large. I have about 47 data validation drop down boxes, and most, but not all have 50+ drop down choices. Each choice of the drop down causes a corresponding pre-designed shape to become visible, while all other shapes corresponding to the other drop down choices that were not chosen are made to be not visible. Each of the shapes will have a distinctive name, so it is hundreds of names.
This is the code I was wanting to use for just one of the drop down cells(had to trim it because of the length), I need to write 46 more of these for the other cells. Is there some other method I can go about this?

Please Login or Register  to view this content.

There's a couple of ways to approach this. You can write a sub for each case and just call that sub. I notice that most everything is set to visible = false so why not set everything false, and then in each case just set visible = true for only the items that need to be visible.

Please Login or Register  to view this content.

I figured out a way. I built individual macros for each group of shapes to be invisible, then called on that macro in each case when I specified the individual shape to be visible, that way everything except the shape I want to be visible is invisible. I also broke down everything for each drop down into subs that begin with "select case range", and included all macro names into the main "worksheet change." I don't know if this is the best way, but it works beautifully. Thank you for your assistance Yraen.

You're welcome. I'm glad it's working out for you. As for a best way, you'll always be learning new tricks so the best way is usually the way you understand it at the time you write it. There are too many differences between each coder to say there is a "best" way.