UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Chart VBA, Office 2013    
 
   
momcaro
post May 16 2018, 09:17 AM
Post#1



Posts: 297
Joined: 25-March 11
From: Wish I was in Colorado


Hello,
I have some VBA code that allows me to customize the x date axis on a chart:

Sub ChangeAxis()
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.Axes(xlValue).MinimumScale = Range("XMin").Value
ActiveChart.Axes(xlValue).MaximumScale = Range("XMax").Value
ActiveChart.Axes(xlValue).MajorUnit = Range("XMU").Value
End Sub

I added a copy of the chart and I would like to have the same code be ran on it, but I don't know how to tell Excel "do this on chart 3, and then do the same thing on chart 4".
Thanks for your help!
Caroline

--------------------
Caroline
Mooing Sheep
Go to the top of the page
 
Doug Steele
post May 16 2018, 09:46 AM
Post#2


UtterAccess VIP
Posts: 21,736
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Wouldn't it simply be

CODE
Sub ChangeAxis()
  
  ActiveSheet.ChartObjects("Chart 3").Activate
  ActiveChart.Axes(xlValue).MinimumScale = Range("XMin").Value
  ActiveChart.Axes(xlValue).MaximumScale = Range("XMax").Value
  ActiveChart.Axes(xlValue).MajorUnit = Range("XMU").Value
  
  ActiveSheet.ChartObjects("Chart 4").Activate
  ActiveChart.Axes(xlValue).MinimumScale = Range("XMin").Value
  ActiveChart.Axes(xlValue).MaximumScale = Range("XMax").Value
  ActiveChart.Axes(xlValue).MajorUnit = Range("XMU").Value
  
End Sub

You could also try

CODE
Sub ChangeAxis()
  
  With ActiveSheet.ChartObjects("Chart 3").Axes(xlValue)
    .MinimumScale = Range("XMin").Value
    .MaximumScale = Range("XMax").Value
    .MajorUnit = Range("XMU").Value
  End With
  
  With ActiveSheet.ChartObjects("Chart 4").Axes(xlValue)
    .MinimumScale = Range("XMin").Value
    .MaximumScale = Range("XMax").Value
    .MajorUnit = Range("XMU").Value
  End With
  
End Sub



--------------------
Go to the top of the page
 
dflak
post May 16 2018, 10:02 AM
Post#3


Utter Access VIP
Posts: 6,142
Joined: 22-June 04
From: North Carolina


This may be overkill. I developed this because I had a dynamic chart that could display a number of metrics: some with large numbers, others with percents. The X-Axis and Y-axis took on different meanings depending on the metric selected.

CODE
Sub SetChart(SheetName As String, ChartName As String, Optional XAxis As String, Optional Y1Axis As String, Optional Y2Axis As String, _
    Optional XFormat As String, Optional Y1Format As String, Optional Y2Format As String)

' SheetName = Sheet name
' ChartName = Chart name
' XAxis = X-Axis title (Optional)
' Y1Axis = Primary Axis title (Optional)
' Y2Axis = Secondary Axis title (Optional)
' XFormat = X-Axis format (Optional)
' Y1Format = Primary Axis format (Optional)
' Y2Format = Secondary Axis format (Optional)

Dim sh As Worksheet

' Initalize Variables
Set sh = Sheets(SheetName)
sh.ChartObjects(ChartName).Activate

' Add new Axis
ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
ActiveChart.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
ActiveChart.SetElement (msoElementSecondaryValueAxisTitleAdjacentToAxis)

' Put in text and format the axis
If Len(XAxis) > 0 Then
    ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Caption = XAxis
Else
    ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleNone)
End If

If Len(Y1Axis) > 0 Then
    ActiveChart.Axes(xlValue).HasTitle = True
    ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = Y1Axis
Else
    ActiveChart.SetElement (msoElementPrimaryValueAxisTitleNone)
End If

If Len(Y2Axis) > 0 Then
    ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Text = Y2Axis
Else
    ActiveChart.SetElement (msoElementSecondaryValueAxisTitleNone)
End If

If Len(XFormat) > 0 Then
    ActiveChart.Axes(xlCategory).TickLabels.NumberFormat = XFormat
End If

If Len(Y1Format) > 0 Then
    ActiveChart.Axes(xlValue, xlPrimary).TickLabels.NumberFormat = Y1Format
End If

If Len(Y2Format) > 0 Then
    ActiveChart.Axes(xlValue, xlSecondary).TickLabels.NumberFormat = Y2Format
End If

End Sub

Sub test()
SetChart "Charts", "Chart 1", "Months", "Qty", "Percent", "General", "#,###", "0.0%"
End Sub

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
momcaro
post May 16 2018, 10:30 AM
Post#4



Posts: 297
Joined: 25-March 11
From: Wish I was in Colorado


Thank you both! I utilized the simple code from Doug, which I thought I had put together but mine didn't work frown.gif
Will check other code and keep it in my sleeve if needed!
uarulez2.gif

--------------------
Caroline
Mooing Sheep
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    27th May 2018 - 05:04 AM