PDA

View Full Version : When changing the active worksheet - need VBA that will make it's chart display?



stanhilliard
04-17-2020, 08:40 PM
I am able to change worksheets while executing VBA by adding DoEvents. However, a chart on the newly selected sheet does not display. How can I with VBA display the chart.

paulked
04-18-2020, 04:19 AM
Sorry, I can't replicate the error. Can you post your workbook?

stanhilliard
04-21-2020, 04:49 PM
Thanks for offering but I was able myself to switch between worksheets and show their imbeded charts while my VBA simulation is running. I accomplished this by using DoEvents to allow capturing my keystrokes when I click the name of another sheet.

However - a problem remains:
My simulation program displays information to sheet="samplingplan" every 100,000 cycles of my simulation. But when Excel is switched to another sheet - while VBA is running - the VBA ColumnOnSamplingplingplan.Clearcontent statement makes the chart on sheet="samplingplan" display on top of the other currently active sheet. The cart that displays is bound to the cells that are cleared by Clearcontent. This only happens when Application.ScreenUpdating = True.

I can set Application.ScreenUpdating = False prior to Clearcontent and that stops the unwanted display, but that's cumbersome as Clearcontent is always followed by adding new content to the cleared cells. What is a solution to avoid this weird behavior?

Excel 2016, Windows 10

stanhilliard
04-21-2020, 11:25 PM
Here is an example of the offending code:


Dim AveragesColumn As Range
Dim samplingplan As Worksheet: Set samplingplan = Sheet1

With samplingplan
Set DataColumn = .Range(.Cells(R0 + 1, 4), .Cells(R0 + Fixedn, 4))
Set AveragesColumn = .Range(.Cells(R0 + 1, 5), .Cells(R0 + Fixedn + 1, 5))
End With

Call ClearDataCells

End Sub


Sub ClearDataCells()
AveragesColumn.ClearContents
End Sub

Artik
04-22-2020, 01:49 AM
Probably the problem lies in the code that builds the chart. It is in this procedure that you probably refer to the active sheet, not to the "samplingplan" sheet.
I don't know if I understood your problem at all, but without an example workbook, it's hard to guess.

Artik

paulked
04-22-2020, 02:06 AM
As Artik sats, without a workbook it is near impossible to guess whats going on.


I can set Application.ScreenUpdating = False prior to Clearcontent and that stops the unwanted display, but that's cumbersome as Clearcontent is always followed by adding new content to the cleared cells.

What is cumbersome with this?


Sub ClearDataCells()
Application.ScreenUpdating = False
AveragesColumn.ClearContents
Application.ScreenUpdating = True
End Sub