How to switch between worksheets while VBA is running?

[stanhilliard]

I have a VBA simulation that runs for about 1.5 hours. It contains a scatter chart on one worksheet that I want to project to a screen while VBA is running. The chart is too small to use for that purpose -- as it shares space with a lot of other things on the worksheet. So I copied the chart to a new worksheet and enlarged it. The problem with that approach is that the workbook is unresponsive to switching between sheets while the VBA is running.

There is one sheet in particular that I want to be able to switch back and forth with the chart between between at will.

How can I display a screen size chart that I can switch at will with another sheet?

[Paul_Hossler]

1. You can try to add a "DoEvents" (or several) inside of the loops

2. Not sure, but did you try Worksheets(".....").Select and then maybe a DoEvents?

[SamT]

Definitely try Paul's approach first.


So I copied the chart to a new worksheet and enlarged it.

Try recreating the chart instead of just copying it.

There is nothing in the VBIDE that inherently will stop Excel, so there must be some algorithm in your code that is causing the issue.

It might be best for your simulation that you eliminate any "Cross Talk" between sheets, that would mean copying the chart's data to that Worksheet as Values, and recreate the chart from that data. I personally would try eliminating all formulas in that particular workbook.

If you're using Excel >= 2007, you can look into using the Camera function to create images for display.

I don't see how, but the solution might be as simple as never referring to Sheet "Tab" names and only using Sheet Object CodeNames in your code. Also note that Excel uses two things named "chart". One "chart" is a cool graphic on a Worksheet or on a Chart "sheet". The other is the Chart "sheet" Object. Insure your code is referring to the correct "sheet" Type. In the VBIDE, press F2 to see the Project Explorer to determine which "sheet" Object you have.

[Paul_Hossler]

If none of those ideas work, then attach a small workbook, but I won't run it for 90 minutes, just long enough to see if the chart updates

[snb]

Use 2 Excel instances

[stanhilliard]

Thanks for the suggestions so far, I solved part of my problem.

While executing, VBA would add data to a worksheet. That would select that worksheet. The sheet with the chart that I was looking at would no longer be visible.

I was able to prevent that by removing:

Worksheets("Samplingplan").Activate
prior to writing to "Samplingplan", and addressing the worksheet instead by code like:

Worksheets("samplingplan").Cells(12, 10) = J_Decisions

This change allowed VBA to write to worksheet "samplingplan" without displaying it.

I am still having a problem being able to select manually between sheets. It works some with doevents, but not it's not very snappy. I will try some of the other suggestions unless I can make switching work faster.