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,916
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



--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
http://www.accessmvp.com/DJSteele/AccessIndex.html
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
dflak
post May 16 2018, 10:02 AM
Post#3


Utter Access VIP
Posts: 6,202
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    22nd September 2018 - 03:44 PM