Condensing ActiveSheet.Shapes().Visible=True

**eculver** · 04-14-2013, 09:02 AM

Hello all, I recently finished my first attempt at VBA and everything is working. However the worksheet is running much slower than I can accept, and I am assuming it is because of the amount of coding I have incorporated. I have already condensed it once by adding "Range.Array", but it made no noticeable change. This worksheet has 36 groups of +36 shapes that I stacked on one another and made invisible, only one shape in each group can be visible at a given time based on the value of a cell. All shapes are different within the group, but each group is made up of the same shapes from the other groups that I gave unique names. I was contemplating renaming all the shapes "shape1(-1296)" and using the dim/variable thing for the "blank" macros, but I can't figure a good way to further condense the visible=true portion. Perhaps a copy/paste/delete into specific coordinates, or have all similar shapes have the same name and call them based on variable coordinates using just one macro, if this would even be possible. If anyone has any suggestions I would be grateful.

Please Login or Register  to view this content.

**Norie** · 04-14-2013, 09:10 AM

I doubt very much it's the amount of code that's causing things to slow down, far more likely to be what the code does, and/or doesn't do.

For example, you don't turn off screen updating.

Can you upload an example workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**ag273n** · 04-14-2013, 09:24 AM

I am assuming that you have about 36 sub taps code...
I may add that:
since the code occurs every time you press the cells or move the active cell, then it might be better to dedicate a refresh command button on the sheet, reducing the occurrence of the code from executing..

other than this, you might want to check the file size...
if its more than about 8 mb (I think), excel tends to respond sluggishly than usual..

**eculver** · 04-14-2013, 10:06 AM

Thanks for the quick replies. I uploaded my workbook. That makes sense about the code running every time a cell is chosen slowing things down. I would rather not use combo boxes if I could avoid it, mostly because I don't like the look of them compared to the data validation cells and I will have to recode alot of calculations I'm importing from another version of this worksheet. I'm looking into writing exit codes into these macros and turn off/on the enable events to see if that will speed things up, if I can figure out a good way to do it. VBA is a learning experience to say the least.

**eculver** · 04-14-2013, 06:27 PM

Alright, this is what I've been doing

Please Login or Register  to view this content.

I put this into about 75% of the modules so far and I've noticed a significant improvement in the speed. Once I finish it should be plenty fast enough to satisfy me.

**eculver** · 04-14-2013, 06:43 PM

actually, I take that back. I saved it and when I reopened it, it was just as slow as before. I'll mess with it more tomorrow.

**Norie** · 04-14-2013, 07:08 PM

Are there specific cells you want to monitor changes in?

**eculver** · 04-14-2013, 10:27 PM

Yes, all the data validation dropdowns on the workbook I uploaded. I'm not sure why the changes I made this evening worked while I had the sheet open, but after I closed it and reopened it got slow again. Maybe I did something wrong. From what was suggested, I am now looking for a way to stop running every unnecessary macro upon every dropdown change. Instead just run the macro for the specific cell that gets changed. Seems there should be an easy way to tweak this code to check that if there is no change in one cell, it skips the code and moves on to the next in line, until it finds when a cell change happens. Just to keep things from slowing down.

Please Login or Register  to view this content.

**eculver** · 04-15-2013, 07:52 PM

Yea, well I figured out this is pointless using worksheet change for 46+ Data Validation cells. I tweaked the code a little more and it was fast at first, but as more of the Data Validation cells contained information, the more code ran and the slower it got. Wanting to keep the format of the DV dropdowns, mainly because activeX or form control combo boxes won't allow wrapping text, I created activeX boxes hidden off the page and linked them to the DV cells in order to attach the macros to them. Thing runs like a dream now. And VBA isn't quite as intimidating as it was last week.