Excel: ScreenUpdating and Worksheet.Activate event 1

[sjh]

Hi,

In my Excel VBA code, I need to activate few sheets to create charts on them, but I don't want to display switching between worksheets to users.

Is it possible to hide this switching activity? I tried using Application.ScreenUpdating = False, but it doesn't seem to work.

Any ideas??

Thank you,
SJH

 

[SkipVought]

That's EXACTLY what you need.

What is the Primary routine and where do you have this code?

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[sjh]

My code looks like this. When the btnGO is clicked, it updates all the worksheets that contain the reports. It takes a while to do calculations and draw the chart, so maybe this lag cannot be avoided.

Thank you,
SJH

Private Sub SynchronizeAllReports()
Application.ScreenUpdating = False
Dim sSelectedScenario As String
sSelectedScenario = cboScenario.Value

wFirstRpt.Activate
wFirstRpt.cboScenario = sSelectedScenario

wSecondRpt.Activate
wSecondRpt.cboScenario = sSelectedScenario

wThirdRpt.Activate
wThirdRpt.cboScenario = sSelectedScenario

Application.ScreenUpdating = True
End Sub

Private Sub btnGo_Click()
SynchronizeAllReports
wFirstRpt.Activate
End Sub

 

[SkipVought]

Why not this?

Private Sub SynchronizeAllReports()
    Application.ScreenUpdating = False
    Dim sSelectedScenario As String
    sSelectedScenario = cboScenario.Value
    
    wFirstRpt.cboScenario = sSelectedScenario
    
    wSecondRpt.cboScenario = sSelectedScenario
    
    wThirdRpt.cboScenario = sSelectedScenario

    Application.ScreenUpdating = True
End Sub

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[sjh]

If I don't make the worksheet active before creating a chart, it throws an error message with (.HasTitle = False
) line.

The following line actually fires an event that creates a new chart on wFirstRpt.

wFirstRpt.cboScenario = sSelectedScenario

__________________________
Here's a snippet of my code for creating a chart. Maybe there's another way to add one???

    Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=wFirstRpt.Range("E18")
                    
    For i = 0 To iArraySize
        ActiveChart.SeriesCollection.NewSeries
    Next i
                    
    ActiveChart.SeriesCollection(i).XValues = sXValues
    ActiveChart.SeriesCollection(i).Values = sParameter
    ActiveChart.SeriesCollection(i).Name = g_asName(i - 1)

    ActiveChart.Axes(xlValue).TickLabels.NumberFormat = "$#,##0"
        
    ActiveChart.Location Where:=xlLocationAsObject, Name:="First Report"
    ActiveSheet.ChartObjects(1).Name = "BarChart"

    With ActiveChart
        .HasTitle = False
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
    End With

 

[SkipVought]

That may be because you are using ActiveChart instead of setting a chart object when you add the chart and referencing the object.

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[sjh]

Could you please show me how to create a chart using a chart object?

I would be very grateful...
SJH

 

[SkipVought]

I chaged a few things for my test like the array in for...next and some sheet references, but this creates a charr on your sheet.

   Dim g_asName, oCh As Object, wFirstRpt As Worksheet
   Set wFirstRpt = Worksheets("First Report")
    Set oCh = Charts.Add
    With oCh
      .ChartType = xlColumnClustered
      .SetSourceData Source:=wFirstRpt.[A1].CurrentRegion
                    
       For i = 0 To 0
           .SeriesCollection.NewSeries
       Next i
                       
       .SeriesCollection(1).XValues = [Name]
       .SeriesCollection(1).Values = [Value]
       .SeriesCollection(1).Name = [AName]
   
       .Axes(xlValue).TickLabels.NumberFormat = "$#,##0"
           
       .Location Where:=xlLocationAsObject, Name:=wFirstRpt.Name
    End With
    With wFirstRpt
      .ChartObjects(1).Name = "BarChart"

      With .ChartObjects(1).Chart
          .HasTitle = False
          .Axes(xlCategory, xlPrimary).HasTitle = False
          .Axes(xlValue, xlPrimary).HasTitle = False
      End With
   End With

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}