Chart Format Code

C

canam

Board Regular
Joined
Dec 14, 2004
Messages
156
I have the following code that formats a couple of charts based on data that is received every minute. The problem is that it makes the sheet where the formatting is occuring the active/visible sheet. This workbook contains multiple worksheets and I don't want it to switch to "process" every time it formats the charts. I would like this to happen in the background. Any help is appreciated.

Code: 
Sheets("Process").ChartObjects("chart 8").Activate
ActiveChart.PlotArea.Select
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
    .MinimumScale = (Range("h19") - 2)
    .MaximumScale = (Range("d19") + 2)
    .MinorUnit = 1
    .MajorUnit = 1
    .Crosses = xlAutomatic
    .ReversePlotOrder = False
    .ScaleType = xlinear
    End With
    
Sheets("Process").ChartObjects("chart 9").Activate
ActiveChart.PlotArea.Select
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
    .MinimumScale = (Range("h31") - 2)
    .MaximumScale = (Range("d30") + 2)
    .MinorUnit = 1
    .MajorUnit = 1
    .Crosses = xlAutomatic
    .ReversePlotOrder = False
    .ScaleType = xlinear
    End With
End If


End Sub
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
Try

Code: 
Dim sh As Worksheet
Set sh = ActiveSheet
Application.ScreenUpdating = False
Sheets("Process").ChartObjects("chart 8").Activate
ActiveChart.PlotArea.Select
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
    .MinimumScale = (Range("h19") - 2)
    .MaximumScale = (Range("d19") + 2)
    .MinorUnit = 1
    .MajorUnit = 1
    .Crosses = xlAutomatic
    .ReversePlotOrder = False
    .ScaleType = xlinear
    End With
   
Sheets("Process").ChartObjects("chart 9").Activate
ActiveChart.PlotArea.Select
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
    .MinimumScale = (Range("h31") - 2)
    .MaximumScale = (Range("d30") + 2)
    .MinorUnit = 1
    .MajorUnit = 1
    .Crosses = xlAutomatic
    .ReversePlotOrder = False
    .ScaleType = xlinear
    End With
End If
sh.Activate
Application.ScreenUpdating = True
End Sub
 
Upvote 0
There is almost never a need to activate or select things. Untested code:
Code: 
    With ActiveWorkbook.Sheets("Process")
    With .ChartObjects("chart 8").Chart.Axes(xlValue)
        .MinimumScale = (Range("h19") - 2)
        .MaximumScale = (Range("d19") + 2)
        .MinorUnit = 1
        .MajorUnit = 1
        .Crosses = xlAutomatic
        .ReversePlotOrder = False
        .ScaleType = xlLinear
        End With
       
    With .ChartObjects("chart 9").Chart.Axes(xlValue)
        .MinimumScale = (Range("h31") - 2)
        .MaximumScale = (Range("d30") + 2)
        .MinorUnit = 1
        .MajorUnit = 1
        .Crosses = xlAutomatic
        .ReversePlotOrder = False
        .ScaleType = xlLinear
        End With
        End With
For some strange reason you were using xlinear rather than xlLinear. It should not have compiled if you had Option Explicit set at the top of your module. And, if you didn't, it was generating unexpected code!

I have the following code that formats a couple of charts based on data that is received every minute. The problem is that it makes the sheet where the formatting is occuring the active/visible sheet. This workbook contains multiple worksheets and I don't want it to switch to "process" every time it formats the charts. I would like this to happen in the background. Any help is appreciated.

Code: 
Sheets("Process").ChartObjects("chart 8").Activate
ActiveChart.PlotArea.Select
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
    .MinimumScale = (Range("h19") - 2)
    .MaximumScale = (Range("d19") + 2)
    .MinorUnit = 1
    .MajorUnit = 1
    .Crosses = xlAutomatic
    .ReversePlotOrder = False
    .ScaleType = xlinear
    End With
    
Sheets("Process").ChartObjects("chart 9").Activate
ActiveChart.PlotArea.Select
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
    .MinimumScale = (Range("h31") - 2)
    .MaximumScale = (Range("d30") + 2)
    .MinorUnit = 1
    .MajorUnit = 1
    .Crosses = xlAutomatic
    .ReversePlotOrder = False
    .ScaleType = xlinear
    End With
End If


End Sub
Click to expand...
 
Upvote 0
In Tushar's code:
Code: 
    With ActiveWorkbook.Sheets("Process")
    With .ChartObjects("chart 8").Chart.Axes(xlValue)
        .MinimumScale = (Range("h19") - 2)
        .MaximumScale = (Range("d19") + 2)
        .MinorUnit = 1
        .MajorUnit = 1
        .Crosses = xlAutomatic
        .ReversePlotOrder = False
        .ScaleType = xlLinear
        End With
       
    With .ChartObjects("chart 9").Chart.Axes(xlValue)
        .MinimumScale = (Range("h31") - 2)
        .MaximumScale = (Range("d30") + 2)
        .MinorUnit = 1
        .MajorUnit = 1
        .Crosses = xlAutomatic
        .ReversePlotOrder = False
        .ScaleType = xlLinear
        End With
        End With
Click to expand...

This With/End With is extraneous.
Code: 
    With ActiveWorkbook.Sheets("Process")
      ....
    End With
I would amend Tushar's code as follows:
Code: 
    Dim ws As Worksheet
    Set ws = ActiveWorkbook.Sheets("Process")

    With .ChartObjects("chart 8").Chart.Axes(xlValue)
        .MinimumScale = (ws.Range("h19") - 2)
        .MaximumScale = (ws.Range("d19") + 2)
        .MinorUnit = 1
        .MajorUnit = 1
        .Crosses = xlAutomatic
        .ReversePlotOrder = False
        .ScaleType = xlLinear
        End With
       
    With .ChartObjects("chart 9").Chart.Axes(xlValue)
        .MinimumScale = (ws.Range("h31") - 2)
        .MaximumScale = (ws.Range("d30") + 2)
        .MinorUnit = 1
        .MajorUnit = 1
        .Crosses = xlAutomatic
        .ReversePlotOrder = False
        .ScaleType = xlLinear
        End With
 
Upvote 0
You make a good point that the unqualified Range references should not have been present. However, the With/End With pair was not extraneous since it was required for the .ChartObject... references. So, with the use of WS as a worksheet variable, one would get
Code: 
    Dim WS As Worksheet
    Set WS = ActiveWorkbook.Sheets("Process")
    With WS.ChartObjects("chart 8").Chart.Axes(xlValue)
        .MinimumScale = (WS.Range("h19") - 2)
        .MaximumScale = (WS.Range("d19") + 2)
        .MinorUnit = 1
        .MajorUnit = 1
        .Crosses = xlAutomatic
        .ReversePlotOrder = False
        .ScaleType = xlLinear
        End With
        
    With WS.ChartObjects("chart 9").Chart.Axes(xlValue)
        .MinimumScale = (WS.Range("h31") - 2)
        .MaximumScale = (WS.Range("d30") + 2)
        .MinorUnit = 1
        .MajorUnit = 1
        .Crosses = xlAutomatic
        .ReversePlotOrder = False
        .ScaleType = xlLinear
        End With
In Tushar's code:
Code: 
    With ActiveWorkbook.Sheets("Process")
    With .ChartObjects("chart 8").Chart.Axes(xlValue)
        .MinimumScale = (Range("h19") - 2)
        .MaximumScale = (Range("d19") + 2)
        .MinorUnit = 1
        .MajorUnit = 1
        .Crosses = xlAutomatic
        .ReversePlotOrder = False
        .ScaleType = xlLinear
        End With
       
    With .ChartObjects("chart 9").Chart.Axes(xlValue)
        .MinimumScale = (Range("h31") - 2)
        .MaximumScale = (Range("d30") + 2)
        .MinorUnit = 1
        .MajorUnit = 1
        .Crosses = xlAutomatic
        .ReversePlotOrder = False
        .ScaleType = xlLinear
        End With
        End With
Click to expand...

This With/End With is extraneous.
Code: 
    With ActiveWorkbook.Sheets("Process")
      ....
    End With
I would amend Tushar's code as follows:
Code: 
    Dim ws As Worksheet
    Set ws = ActiveWorkbook.Sheets("Process")

    With .ChartObjects("chart 8").Chart.Axes(xlValue)
        .MinimumScale = (ws.Range("h19") - 2)
        .MaximumScale = (ws.Range("d19") + 2)
        .MinorUnit = 1
        .MajorUnit = 1
        .Crosses = xlAutomatic
        .ReversePlotOrder = False
        .ScaleType = xlLinear
        End With
       
    With .ChartObjects("chart 9").Chart.Axes(xlValue)
        .MinimumScale = (ws.Range("h31") - 2)
        .MaximumScale = (ws.Range("d30") + 2)
        .MinorUnit = 1
        .MajorUnit = 1
        .Crosses = xlAutomatic
        .ReversePlotOrder = False
        .ScaleType = xlLinear
        End With
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Set chart properties without selecting chart
Replies
4
Views
343
smozgur
smozgur
L
  • Solved
VBA Help Modify Code - Prevent chart from resizing with columns / rows
Replies
3
Views
695
Anthony47
A
A
  • Question
Series Chart and Data with IF Statement
Replies
2
Views
273
NdNoviceHlp
NdNoviceHlp
D
  • Question
Very minor change in code had a dramatic impact on the speed. Why?
Replies
2
Views
608
deZine
D
A
  • Question
Chart and Data
Replies
8
Views
354
NateSC
NateSC

Forum statistics

Threads
1,214,922
Messages
6,122,281
Members
449,075
Latest member
staticfluids

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top