0

I'm working with a very complicated Excel template, and I've been getting reports that the more copies of the same template are open the more it becomes exponentially slow.

I did some tests and noticed that the code Worksheet_SelectionChange written in the VBA code for a sheet called "Deliverables" sheet gets triggered if I'm on a new file and I do modifications to the second file, which does not make sense to me, but it does happen.

Before you guys jump on me, i already googled this, and I have not found a complete list, so I'm hopping your experience will help me.

I already have a validation function like the one below that would handle that situation, but I was wondering:

Is there an extensive list of triggers that can be activated while a different file is selected? (i just want to make sure I catch all situations)

This is the check I currently use:

thisworkbook.name <> activeWorkbook.name

Update: I did some tests and was able to pin point what and when was changing: File 1 is the initial template, File 2 is an empty file with no macros, It just contains an auto formated table. In file 1 I have conditional formatting with this formula: =AND(IsColored=0,COLUMN()=CELL("col")) which colors currently selected column, same for rows. If in file 2, the one with no macros, i do a copy paste, the conditional formatting in file 1 updates, and applies conditional formatting according to file 2 last updated cell .

Dumitru Daniel
  • 571
  • 4
  • 19
  • 1
    Are you `Select`ing in your code? The event will only fire if that worksheet is selected. Which is why I ask if you are `Select`ing, in which case, [don't](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba), unless you want the selection change code to run. – BigBen Apr 23 '20 at 16:04
  • 2
    Also, provide the `SelectionChange` event code. Maybe we can help make that code more efficient or work better for you. And to clarify what @BigBen said, if anywhere in your code you `.Select` on a range in the "Deliverables" worksheet the `SelectionChange` event will fire each time. So avoid that. Lastly, FWIW, I rarely - super rarely - ever use `SelectionChange` event. It's possible there's a better way :) – Scott Holtzman Apr 23 '20 at 16:13
  • I never use .select in the macros, except when testing, sometimes to make sure the correct range is selected. I added a test result at the end of my initial post. i have ```COLUMN()=CELL("col")``` in a conditional formatting formula. Should conditional formatting update when i copy paste in another file? and is there a way to restrict this formula only to current file? – Dumitru Daniel Apr 24 '20 at 09:08

0 Answers0