Change Chart to 2-Y Axis in VBA

OldSwimmer1650

New Member
Joined
Dec 3, 2020
Messages
23
Office Version
  1. 365
Platform
  1. Windows
The code is selecting a range of cells
Range(ActiveCell, ActiveCell.Offset(daysinmonth - 1, 1)).Select
right before it creates the chart tab. When the chart is created it plots only the second column of data(first column is correctly identified as X-axis). I'm trying to figure out how to plot the third column of date on the second Y-axis in the chart using VBA.


1646781061830.png



Code:
Option Explicit         ' Force explicit variable declaration.


Sub createweightchart()
 

    Dim chartsheet          As Chart        'new chart sheet to create
   
    Dim daysinmonth         As Integer      'number of days in the month
   
    Dim finddate            As Range        'cell of date to start
     
    Dim chartname           As String       'chart tab name
    Dim completedatetofind  As String       'this is the start date to find in table
    Dim monthchartname      As String       'name of new chart tab
    Dim monthcreate         As String       'this is the month to create chart
    Dim yearcreate          As String       'year for the corresponding month
   
   
    monthcreate = InputBox(Prompt:="What Month to Create?", _
                    Title:="To Create a Monthly Weight & BP Chart", _
                    Default:="Enter Month Here here")
 
    yearcreate = InputBox(Prompt:="What Year/Month to Create?", _
                    Title:="To Create a Monthly Weight & BP Chart", _
                    Default:="Enter Month Here here")
                   
    Worksheets("Weight Data").Select                                                'select tab where the weight data is
    completedatetofind = monthcreate & "/" & "1" & "/" & yearcreate                 'start date to find in table to create chart
    Set finddate = Range("D8:I6400").Find(what:=completedatetofind)                 'find the first of the month to create
    finddate.Select                                                                 'select that cell
    daysinmonth = Day(Application.WorksheetFunction.EoMonth(completedatetofind, 0)) 'how many days in this month
    Range(ActiveCell, ActiveCell.Offset(daysinmonth - 1, 1)).Select                 'select the range of data to plot date+2 columns of data
   
   
'Create Chart Tab
   
    If monthcreate = 1 Then
            monthchartname = "Jan"
        ElseIf monthcreate = 2 Then
            monthchartname = "Feb"
        ElseIf monthcreate = 3 Then
            monthchartname = "Mar"
        ElseIf monthcreate = 4 Then
            monthchartname = "Apr"
        ElseIf monthcreate = 5 Then
            monthchartname = "May"
        ElseIf monthcreate = 6 Then
            monthchartname = "Jun"
        ElseIf monthcreate = 7 Then
            monthchartname = "July"
        ElseIf monthcreate = 8 Then
            monthchartname = "Aug"
        ElseIf monthcreate = 9 Then
            monthchartname = "Sept"
        ElseIf monthcreate = 10 Then
            monthchartname = "Oct"
        ElseIf monthcreate = 11 Then
            monthchartname = "Nov"
        Else: monthchartname = "Dec"
    End If
   
    Charts.Add.Name = monthchartname & " " & yearcreate & " Weight Chart"
   
   

 
End Sub
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
3,049
You can trial the following code as long as you have selected all the data when making the chart. Adjust the sheet name and chart index number to suit. HTH. Dave
Code:
With Sheets("Sheet1").ChartObjects(1).Chart
.SeriesCollection(2).AxisGroup = 2
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlCategory, xlSecondary) = True
.HasAxis(xlValue, xlPrimary) = True
.HasAxis(xlValue, xlSecondary) = True
.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic
.Axes(xlCategory, xlSecondary).CategoryType = xlAutomatic
End With
To use vba to chart this without the need for selection...
Code:
Dim ChartRange As Range, X1Value As Range, Y1Value As Range, LastRow As Integer
With Sheets("Sheet1")
    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
Set X1Value = Sheets("Sheet1").Cells(1, 1)
Set Y1Value = Sheets("Sheet1").Cells(LastRow, 3)
Set ChartRange = Sheets("Sheet1").Range(X1Value, Y1Value)
Charts.Add.Location Where:=xlLocationAsObject, Name:="Sheet1"
Sheets("Sheet1").ChartObjects(1).Chart.ChartType = xlXYScatterSmoothNoMarkers
Sheets("Sheet1").ChartObjects(1).Placement = xlFreeFloating
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,096
Messages
5,852,153
Members
431,488
Latest member
ePayslip

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
Top