Overlapping, transparent sheets!!??

[ernie37]

I have a situation where I have two overlapping, semi transparent sheets popping up in my workbook after a bunch of VBA calculations, even though I select one of the sheets as my focus.

The offending statement seems to be:

Application.Calculation = xlCalculationAutomatic

that should just turn the calculations back on. But, it seems to be the one triggering the double vision. Below that statement I have:

Worksheets(“Summary”).Activate
Worksheets(“Summary”).Range(“A1:A1”).Select

to try and select just the Summary sheet. I realize that is redundant, but it refuses to select just the Summary sheet. There another sheet that sits semi-transparently on top of Summary.

?????????????? Any ideas, anyone??? ???????????????

 

[Smitty]

It sounds like a memory issue, not so much an Excel issue.

Can you post all of the code?

 

[ernie37]

Oh, Jeeze! It is a huge workbook with many sheets and pages and pages of VBA. So, that’s not going to be helpful. I have found that it has something to do with one of the sheets that has a lot of calculated cells on it with formulas like VLOOKUP, etc. When I manually turn on Automatic Calculation, the thing pops up. When I go to manual, it goes away. Maybe that is a clue.

 

[Smitty]

It is a huge workbook with many sheets and pages and pages of VBA

Sounds like a memory issue.

 

[ernie37]

(WORKAROUND) I traced the problem to a sheet that had a lot of vlookup calculations that referred to other sheets. For some reason, when automatic calculation was turned on, this thing overwrote itself partially onto another sheet (bizarre display). Here is a partial solution ... more of a workaround. In my workbook I have several sheets with lots of calculations. This is where the problem occurred. When I run these sheets, I get the bizarre display where the offending sheet pops up and partially overwrites the currently displayed sheet. So, at the beginning of calculations on the other sheets, I turned off the calculations to the offending sheet using:

ThisWorkbook.Sheets(“SheetName”).EnableCalculation = False

Then, I put a button at the top of the offending sheet so when I want to use calculations on that sheet (where all of the vlookups are that refer to other sheets) I run a macro with the button that says:

ThisWorkbook.Sheets(“SheetName”).EnableCalculation = True ‘turn calculations back on
Worksheets(“SheetName”).Calculate

This is a little clumsy but it solves my problem.

Maybe this gives a better diagnosis of the original problem. Any ideas?