VBA Axes ScaleType Error

M

mikejvir

Board Regular
Joined
Jan 3, 2008
Messages
95
Hello All,

I deal with sending data to customers so I need my graphs to look similar. I wrote a routine to make a chart page look consistent starting back in Excel 2007. I am now at Excel 2016 and I needed to update my macros for compatibility.

All charts must be XY Scatter. The code works if there is only 1 Category axis (X axis) and 1 Value axis (Y axis). It will work with both a linear or logarithmic scale for the X axis.

I needed to add a secondary Value axis and now I can not determine what to do if I have a X axis logarithmic scale. I get the Error

"Run-time error '-2147467259 (80004005)':
Method 'ScaleType' of object 'Axis' failed.

I highlighted the line in Red below as to the failure. I can't figure out what I did wrong. I search the forum but could not find an answer.

I am hoping someone could help. Thanks.

Michael Virostko




Code: 
Option Base 1
Option Explicit
Sub Run_FMT_Chart()
Dim tdiff As Double
Dim tbeg As Double
Dim tend As Double
Dim timebeg As Double
Dim timeend As Double
Dim timestr As String
Dim ntype As Double

Dim ExcelVersion As Integer

    ExcelVersion = Application.Version
If 1 = 0 Then

    timebeg = Time
    tbeg = timebeg
    Sheets("Sheet2").Cells(2, 1).FormulaR1C1 = "Beg (TS)"
    Sheets("Sheet2").Cells(2, 2).FormulaR1C1 = timebeg
    Sheets("Sheet2").Cells(2, 3).FormulaR1C1 = "Beg (#)"
    Sheets("Sheet2").Cells(2, 4).FormulaR1C1 = tbeg
End If

    Call Reformat_Chart_Page(ExcelVersion, 0)
    
If 1 = 0 Then
    timeend = Time
    tend = timeend
    Sheets("Sheet2").Cells(3, 1).FormulaR1C1 = "End (TS)"
    Sheets("Sheet2").Cells(3, 2).FormulaR1C1 = timeend
    Sheets("Sheet2").Cells(3, 3).FormulaR1C1 = "End(#)"
    Sheets("Sheet2").Cells(3, 4).FormulaR1C1 = tend
    
    tdiff = CLng(24) * CLng(3600) * (tend - tbeg)
    Sheets("Sheet2").Cells(4, 3).FormulaR1C1 = "Diff (#)"
    Sheets("Sheet2").Cells(4, 4).FormulaR1C1 = tdiff
    
    timestr = "Time to run Macro = " & Format(tdiff, "0.0000") & "  seconds"
      
      ntype = InputBox(timestr, "Time", 0)
End If
End Sub
Sub Reformat_Chart_Page(ExcelVersion As Integer, mlabel As Integer)
' Reformat_Chart_page Macro
' 2006-01-27 Macro recorded by Michael J. Virostko
' 2011-07-14 Revised for Excel 2007 speed.
'            Set up Overall Page size along with the Header and Footers
'
' 2016-11-11 Revised to work with Excel 2016
'            Aded the ability to log scales and move the text on the scales.
'
Dim ChtType As Variant
Dim borderweight As Variant
Dim Tstring As String
Dim xlogs As Boolean
Dim ylogs As Boolean
Dim testvar As Variant
'
  borderweight = 2

  ActiveWindow.Zoom = 100
  ActiveChart.Activate
  Application.ScreenUpdating = False      ' do all transfers in the background
  Application.DisplayStatusBar = True
'
' Print Page Setup
'
  ChtType = ActiveChart.ChartType
'
'  Use new subroutine to format page
'  First Parameter is to print company name,
'  Second parameter is use landscape mode
'
  Call Format_Worksheet_print(True, True)
  
  [COLOR="#FF0000"]testvar = ActiveChart.Axes(xlCategory, xlPrimary).ScaleType
[/COLOR]  xlogs = False
  If ActiveChart.Axes(xlCategory, xlPrimary).ScaleType = xlLogarithmic Then
    xlogs = True
  End If
  ylogs = False
  If ActiveChart.Axes(xlValue, xlPrimary).ScaleType = xlLogarithmic Then
    ylogs = True
  End If
'
' For Excel 2007,  Need to define the Chart Area size and area
' Define the Chart area (no border)
'  Removed for 2010  have no idea why this will not work.
' Need to add back just for Excel 2007
'
  If ExcelVersion > 12 Then
    ActiveChart.ChartArea.Select
    With Selection
      If ExcelVersion = 12 Then
        .Top = 0
        .Left = 0
        .Width = 745  '735
        .Height = 530 '530
    End If
    .Border.LineStyle = xlNone
    .Interior.ColorIndex = xlNone
    End With
  End If
'
' Define the area for the plot
'
  ActiveChart.PlotArea.Select
  With Selection
    .Top = 75
    .Left = 25
    .Width = 690
    .Height = 410
  End With
'
'  Format the plot area for white background, solid border.
'
    ActiveChart.PlotArea.Select
    With Selection.Border
        .ColorIndex = 1
        .Weight = borderweight + 0.5
        .LineStyle = xlContinuous
    End With
    Selection.Interior.ColorIndex = xlNone
'
' Set up graph to have only major gridlines
'
  With ActiveChart.Axes(xlCategory, xlPrimary)
    .HasMajorGridlines = True
    If xlogs Then
      .HasMinorGridlines = True
    Else
      .HasMinorGridlines = False
    End If
  End With
  With ActiveChart.Axes(xlValue)
    .HasMajorGridlines = True
    If ylogs Then
      .HasMinorGridlines = True
    Else
      .HasMinorGridlines = False
    End If
  End With
'
' Set up major gridlines to be a dashed line
'
'  xlCategory represents  x
'  xlvalue    represents  y
'
'  Format the Chart Title
'
  If ActiveChart.HasTitle = False Then
    ActiveChart.HasTitle = True
  End If
    
  ActiveChart.ChartTitle.Select
  Selection.AutoScaleFont = True
  With Selection.Font
    .Name = "Times New Roman"
    .FontStyle = "Bold"
    .Size = 16
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic
    .Background = xlAutomatic
  End With
'
'  Reformat the X-Axis
'
  If ActiveChart.Axes(xlCategory, xlPrimary).HasTitle = False Then
    ActiveChart.Axes(xlCategory, xlPrimary).HasTitle = True
  Else
    Tstring = ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Caption
    ActiveChart.Axes(xlCategory, xlPrimary).HasTitle = False
    ActiveChart.Axes(xlCategory, xlPrimary).HasTitle = True
    ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = Tstring
  End If
    
  ActiveChart.Axes(xlCategory, xlPrimary).MajorGridlines.Select
  With Selection.Border
    .Color = RGB(0, 0, 0)   ' Black
    .Weight = borderweight + 0.5
    .LineStyle = xlDash
  End With
  ActiveChart.Axes(xlCategory, xlPrimary).Select
  With Selection
    .MajorTickMark = xlCross
    .MinorTickMark = xlInside
    .TickLabelPosition = xlLow
    .Crosses = xlCustom
    If ChtType > 71 And ChtType < 76 Then
      If xlogs Then
        .CrossesAt = 0.00001
      Else
        .CrossesAt = -2000
      End If
      .ReversePlotOrder = False
      .DisplayUnit = xlNone
    End If
  End With
  ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Select
  Selection.AutoScaleFont = True
  With Selection.Font
    .Name = "Times New Roman"
    .FontStyle = "Bold"
    .Size = 14
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic
    .Background = xlAutomatic
  End With
'
'  ActiveChart.Axes(xlCategory, xlPrimary).Select
  With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .ReadingOrder = xlContext
    .Orientation = xlHorizontal
  '  .Top = xlAuto
  End With

  ActiveChart.Axes(xlCategory, xlPrimary).MajorGridlines.Select
  With Selection.Border
    .Color = RGB(0, 0, 0)
    If xlogs Then
      .Weight = borderweight
      .LineStyle = xlSolid
    Else
      .Weight = xlHairline
      .LineStyle = xlDash
    End If
  End With

  If xlogs Then
    If ActiveChart.Axes(xlCategory, xlPrimary).HasMinorGridlines = False Then
      ActiveChart.Axes(xlCategory, xlPrimary).HasMinorGridlines = True
    End If
    
    ActiveChart.Axes(xlCategory, xlPrimary).MinorGridlines.Select
    With Selection.Border
      .Color = RGB(0, 0, 0)
      .Weight = borderweight - 1
      .LineStyle = xlDash
    End With
  End If
'
'  Reformat the Y-Axis
'
  If ActiveChart.Axes(xlValue, xlPrimary).HasTitle = False Then
    ActiveChart.Axes(xlValue, xlPrimary).HasTitle = True
  Else
    Tstring = ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Caption
    ActiveChart.Axes(xlValue, xlPrimary).HasTitle = False
    ActiveChart.Axes(xlValue, xlPrimary).HasTitle = True
    ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = Tstring
  End If
    
  ActiveChart.Axes(xlValue, xlPrimary).MajorGridlines.Select
  With Selection.Border
    .Color = RGB(0, 0, 0)   ' Black
    .Weight = borderweight + 0.5
    .LineStyle = xlDash
  End With
  ActiveChart.Axes(xlValue, xlPrimary).Select
  With Selection
    .MajorTickMark = xlCross
    .MinorTickMark = xlInside
    .TickLabelPosition = xlLow
    .Crosses = xlCustom
    If ChtType > 71 And ChtType < 76 Then
      If xlogs Then
        .CrossesAt = 0.00001
      Else
        .CrossesAt = -2000
      End If
      .ReversePlotOrder = False
      .DisplayUnit = xlNone
    End If
  End With
  ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Select
  Selection.AutoScaleFont = True
  With Selection.Font
    .Name = "Times New Roman"
    .FontStyle = "Bold"
    .Size = 14
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic
    .Background = xlAutomatic
  End With
'
'  ActiveChart.Axes(xlValue, xlPrimary, xlPrimary).Select
  With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .ReadingOrder = xlContext
    .Orientation = xlUpward
  End With

  ActiveChart.Axes(xlValue, xlPrimary).MajorGridlines.Select
  With Selection.Border
    .Color = RGB(0, 0, 0)
    If xlogs Then
      .Weight = borderweight
      .LineStyle = xlSolid
    Else
      .Weight = xlHairline
      .LineStyle = xlDash
    End If
  End With

  If xlogs Then
    If ActiveChart.Axes(xlValue, xlPrimary).HasMinorGridlines = False Then
      ActiveChart.Axes(xlValue, xlPrimary).HasMinorGridlines = True
    End If
    
    ActiveChart.Axes(xlValue, xlPrimary).MinorGridlines.Select
    With Selection.Border
      .Color = RGB(0, 0, 0)
      .Weight = borderweight - 1
      .LineStyle = xlDash
    End With
  End If
'
'  Reformat the Secondary Y-Axis
'
  If ActiveChart.Axes.Count > 2 Then
    
    If ActiveChart.Axes(xlValue, xlSecondary).HasTitle = False Then
      ActiveChart.Axes(xlValue, xlSecondary).HasTitle = True
    Else
      Tstring = ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Caption
      ActiveChart.Axes(xlValue, xlSecondary).HasTitle = False
      ActiveChart.Axes(xlValue, xlSecondary).HasTitle = True
      ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = Tstring
    End If
    
If 1 = 0 Then
    ActiveChart.Axes(xlValue, xlSecondary).MajorGridlines.Select
    With Selection.Border
      .Color = RGB(0, 0, 0)   ' Black
      .Weight = borderweight + 0.5
      .LineStyle = xlDash
    End With
End If
    ActiveChart.Axes(xlValue, xlSecondary).Select
    With Selection
      .MajorTickMark = xlCross
      .MinorTickMark = xlInside
      .TickLabelPosition = xlLow
      .Crosses = xlCustom
      If ChtType > 71 And ChtType < 76 Then
        If xlogs Then
          .CrossesAt = 0.00001
        Else
          .CrossesAt = -2000
        End If
        .ReversePlotOrder = False
        .DisplayUnit = xlNone
      End If
    End With
    ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
    Selection.AutoScaleFont = True
    With Selection.Font
      .Name = "Times New Roman"
      .FontStyle = "Bold"
      .Size = 14
      .Strikethrough = False
      .Superscript = False
      .Subscript = False
      .OutlineFont = False
      .Shadow = False
      .Underline = xlUnderlineStyleNone
      .ColorIndex = xlAutomatic
      .Background = xlAutomatic
    End With
'
'  ActiveChart.Axes(xlValue, xlSecondary, xlPrimary).Select
    With Selection
      .HorizontalAlignment = xlCenter
      .VerticalAlignment = xlCenter
      .ReadingOrder = xlContext
      .Orientation = xlUpward
    End With

    ActiveChart.Axes(xlValue, xlSecondary).MajorGridlines.Select
    With Selection.Border
      .Color = RGB(0, 0, 0)
      If xlogs Then
        .Weight = borderweight
        .LineStyle = xlSolid
      Else
        .Weight = xlHairline
        .LineStyle = xlDash
      End If
    End With

    If xlogs Then
      If ActiveChart.Axes(xlValue, xlSecondary).HasMinorGridlines = False Then
        ActiveChart.Axes(xlValue, xlSecondary).HasMinorGridlines = True
      End If

      ActiveChart.Axes(xlValue, xlSecondary).MinorGridlines.Select
      With Selection.Border
        .Color = RGB(0, 0, 0)
        .Weight = borderweight - 1
        .LineStyle = xlDash
      End With
    End If
  End If
'
' Force Legend to have a border, white soldid fill.
'
    ActiveChart.Legend.Select
    With Selection.Border
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    Selection.Shadow = False
    With Selection.Interior
        .ColorIndex = 2
        .PatternColorIndex = 1
        .Pattern = xlSolid
    End With

'    Selection.Interior.ColorIndex = xlNone
    Selection.AutoScaleFont = False
    With Selection.Font
        .Name = "Times New Roman"
        .FontStyle = "Regular"
        .Size = 12 ' 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
        .Background = xlTransparent
    End With
    
    Application.ScreenUpdating = True      ' do all transfers in the background

End Sub
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
mikejvir said:
Hello All,

I deal with sending data to customers so I need my graphs to look similar. I wrote a routine to make a chart page look consistent starting back in Excel 2007. I am now at Excel 2016 and I needed to update my macros for compatibility.

All charts must be XY Scatter. The code works if there is only 1 Category axis (X axis) and 1 Value axis (Y axis). It will work with both a linear or logarithmic scale for the X axis.

I needed to add a secondary Value axis and now I can not determine what to do if I have a X axis logarithmic scale. I get the Error

"Run-time error '-2147467259 (80004005)':
Method 'ScaleType' of object 'Axis' failed.

I highlighted the line in Red below as to the failure. I can't figure out what I did wrong. I search the forum but could not find an answer.

I am hoping someone could help. Thanks.

Michael Virostko




Code: 
Option Base 1
Option Explicit
...

'
  Call Format_Worksheet_print(True, True)
  
[COLOR=#FF0000]    testvar = ActiveChart.Axes(xlCategory, xlPrimary).ScaleType
[/COLOR]  xlogs = False
...
    Application.ScreenUpdating = True      ' do all transfers in the background

End Sub
Click to expand...

have you not realised that the error is because you need to do this:

Code: 
<code>Charts("Chart1").Axes(xlValue).[B]ScaleType = xlScaleLogarithmic[/B]</code>
The alternative is whether you want to define testvar as an object in which case you net to use the set statement
Code: 
    set [COLOR=#FF0000]    testvar = ActiveChart.Axes(xlCategory, xlPrimary).ScaleType[/COLOR]
This will then allow you to use
Code: 
testvar=<code>[B]xlScaleLogarithmic[/B]</code>
 
Upvote 0
I hope this works

Code: 
Sub Reformat_Chart_Page(ExcelVersion As Integer, mlabel As Integer)
' Reformat_Chart_page Macro
' 2006-01-27 Macro recorded by Michael J. Virostko
' 2011-07-14 Revised for Excel 2007 speed.
'            Set up Overall Page size along with the Header and Footers
'
' 2016-11-11 Revised to work with Excel 2016
'            Aded the ability to log scales and move the text on the scales.
'
    Dim TestVar, ChtType, borderweight
    Dim xlogs As Boolean, ylogs As Boolean
    Dim Tstring As String

    borderweight = 2

    ActiveWindow.Zoom = 100
    ActiveChart.Activate
    With Application
        .ScreenUpdating = False      ' do all changes in the background
        .DisplayStatusBar = True
    End With
'
' Print Page Setup
'
    ChtType = ActiveChart.ChartType
'
'  Use new subroutine to format page
'  First Parameter is to print company name,
'  Second parameter is use landscape mode
'
    Call Format_Worksheet_print(True, True)
    With ActiveChart.Axes(xlCategory, xlPrimary)

        xlogs = False: ylogs = False

        If .ScaleType = xlLogarithmic Then
            xlogs = True
            ylogs = True
        End If

    End With
'
' For Excel 2007,  Need to define the Chart Area size and area
' Define the Chart area (no border)
'  Removed for 2010  have no idea why this will not work.
' Need to add back just for Excel 2007
'
    Select Case ExcelVersion
        Case 12  ' version 12 onwards
            With ActiveChart.ChartArea
                .Top = 0
                .Left = 0
                .Width = 745  '735
                .Height = 530 '530
                .Border.LineStyle = xlNone
                .Interior.ColorIndex = xlNone
            End With
        Case Is > 12
            With ActiveChart.ChartArea
                .Border.LineStyle = xlNone
                .Interior.ColorIndex = xlNone
            End With
        Case Else
            MsgBox "Your version of Excel is too old", vbInformation, "Excel " & ExcelVersion
    End If
'
' Define the area for the plot
'
    With ActiveChart.PlotArea
        .Top = 75
        .Left = 25
        .Width = 690
        .Height = 410

'  Format the plot area for white background, solid border.
        With .Border
            .ColorIndex = 1
            .Weight = borderweight + 0.5
            .LineStyle = xlContinuous
        End With
        .Interior.ColorIndex = xlNone
    End With

' Set up graph to have only major gridlines
    With ActiveChart.Axes(xlCategory, xlPrimary)
        .HasMajorGridlines = True

        If xlogs Then
            .HasMinorGridlines = True
        Else
            .HasMinorGridlines = False
        End If

    End With

    With ActiveChart.Axes(xlValue)
        .HasMajorGridlines = True
    
        If ylogs Then
            .HasMinorGridlines = True
        Else
            .HasMinorGridlines = False
        End If
    End With
'
' Set up major gridlines to be a dashed line
'
'  xlCategory represents  x
'  xlvalue    represents  y
'
'  Format the Chart Title
'
    With ActiveChart
        If .HasTitle = False Then .HasTitle = True
        .ChartTitle.AutoScaleFont = True
        .Font
        .Name = "Times New Roman"
        .FontStyle = "Bold"
        .Size = 16
    End With

'  Reformat the X-Axis
    With ActiveChart.Axes(xlCategory, xlPrimary)
        If .HasTitle = False Then
            .HasTitle = True
        Else
            Tstring = .AxisTitle.Caption
            .HasTitle = True
            .AxisTitle.Characters.Text = Tstring
        End If
    End With

    With ActiveChart.Axes(xlCategory, xlPrimary)
        With .MajorGridlines
            With .Border
                .Color = rgbBlack   ' Black
                .Weight = borderweight + 0.5
                .LineStyle = xlDash
            End With
        End With
        .MajorTickMark = xlCross
        .MinorTickMark = xlInside
        .TickLabelPosition = xlLow
        .Crosses = xlCustom
        Select Case ChtType
            Case 72 To 75
                If xlogs Then
                    .CrossesAt = 0.00001
                Else
                    .CrossesAt = -2000
                End If
                .ReversePlotOrder = False
                .DisplayUnit = xlNone
            Case Else
        End Select

        .AxisTitle.AutoScaleFont = True

        With Selection.Font
            .Name = "Times New Roman"
            .FontStyle = "Bold"
            .Size = 14

'  ActiveChart.Axes(xlCategory, xlPrimary).Select
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .ReadingOrder = xlContext
        .Orientation = xlHorizontal
        '  .Top = xlAuto

            With .MajorGridlines
                With .Border
                    .Color = rgbBlack
                    If xlogs Then
                        .Weight = borderweight
                        .LineStyle = xlSolid
                    Else
                        .Weight = xlHairline
                        .LineStyle = xlDash
                    End If
                End With
            End With

            If xlogs Then
                    
                    If .HasMinorGridlines = False Then .HasMinorGridlines = True
                    
                    With .MinorGridlines.Border
                        .Color = rgbBlack
                        .Weight = borderweight - 1
                        .LineStyle = xlDash
                    End With
                End With
            End If

'
'  Reformat the Y-Axis
'
            If .HasTitle = False Then
                .HasTitle = True
            Else
                Tstring = .AxisTitle.Caption
                .HasTitle = True
                .AxisTitle.Characters.Text = Tstring
            End If

        With .MajorGridlines.Border
            .Color = rgbBlack   ' Black
            .Weight = borderweight + 0.5
            .LineStyle = xlDash
        End With
        .MajorTickMark = xlCross
        .MinorTickMark = xlInside
        .TickLabelPosition = xlLow
        .Crosses = xlCustom

        Select Case ChtType
            Case 72 To 75
                If xlogs Then
                    .CrossesAt = 0.00001
                Else
                    .CrossesAt = -2000
                End If
                .ReversePlotOrder = False
                .DisplayUnit = xlNone
        End Select

        With .AxisTitle
            .AutoScaleFont = True
            With .Font
                .Name = "Times New Roman"
                .FontStyle = "Bold"
                .Size = 14
            End With
'
'  ActiveChart.Axes(xlValue, xlPrimary, xlPrimary).Select
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .ReadingOrder = xlContext
            .Orientation = xlUpward
        End With
    End With

    With ActiveChart.Axes(xlValue, xlPrimary)
        With .MajorGridlines.Border
            .Color = rgbBlack
            If xlogs Then
                .Weight = borderweight
                .LineStyle = xlSolid
            Else
                .Weight = xlHairline
                .LineStyle = xlDash
            End If
        End With
    
        If xlogs Then
            If .HasMinorGridlines = False Then .HasMinorGridlines = True
            
            With .MinorGridlines.Border
                .Color = rgbBlack
                .Weight = borderweight - 1
                .LineStyle = xlDash
            End With
        End If
    End With
'
'  Reformat the Secondary Y-Axis
'
If ActiveChart.Axes.Count > 2 Then
    With ActiveChart.Axes(xlValue, xlSecondary)
        If .HasTitle = False Then
            .HasTitle = True
        Else
            Tstring = .AxisTitle.Caption
            .HasTitle = True
            .AxisTitle.Characters.Text = Tstring
        End If
            
            Rem ### Problem code here ###
        If 1 = 0 Then 'This will never run!!!
            With .MajorGridlines.Border
                .Color = rgbBlack   ' Black
                .Weight = borderweight + 0.5
                .LineStyle = xlDash
            End With
        End If
            Rem ### Problem code ends here ###
        
        .MajorTickMark = xlCross
        .MinorTickMark = xlInside
        .TickLabelPosition = xlLow
        .Crosses = xlCustom
        
        Select Case ChtType
            Case 72 To 75

                If xlogs Then
                    .CrossesAt = 0.00001
                Else
                    .CrossesAt = -2000
                End If

                .ReversePlotOrder = False
                .DisplayUnit = xlNone
        End Select
        
        With .AxisTitle
            .AutoScaleFont = True
            With .Font
                .Name = "Times New Roman"
                .FontStyle = "Bold"
                .Size = 14
            End With
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .ReadingOrder = xlContext
            .Orientation = xlUpward
        End With
        
        With .MajorGridlines.Border
            .Color = rgbBlack
            If xlogs Then
                .Weight = borderweight
                .LineStyle = xlSolid
            Else
                .Weight = xlHairline
                .LineStyle = xlDash
            End If
        End With

        If xlogs Then
            If .HasMinorGridlines = False Then
                .HasMinorGridlines = True
                With .MinorGridlines.Border
                    .Color = rgbBlack
                    .Weight = borderweight - 1
                    .LineStyle = xlDash
                End With
            End If
        End If
    End With
'
' Force Legend to have a border, white solid fill.
'
    With ActiveChart.Legend
        .AutoScaleFont = False
        .Shadow = False

        With .Border
            .Weight = xlThin
            .LineStyle = xlContinuous
        End With

        With .Interior
            .ColorIndex = 2
            .PatternColorIndex = 1
            .Pattern = xlSolid
        End With
        
        '    Selection.Interior.ColorIndex = xlNone
        With .Font
            .Name = "Times New Roman"
            .FontStyle = "Regular"
            .Size = 12 ' 10
            .ColorIndex = 1
            .Background = xlTransparent
        End With
    End With

Application.ScreenUpdating = True      ' do all transfers in the background

End Sub
 
Upvote 0
Hi Rhodie72,

There were some minor fixes that I had to do, but it still did not work. I went back to the Web with other variations of my question and was able to find new information.


ScaleType error | Microsoft Office Forums

told me that the Axes ScaleType error has been around since Excel 2007. Seems Microsoft does not care about this error. Therefore, I had to create my ouwn routine for doing this. Here is the code.

Code: 
Public Function ScaleType_Category() As Variant
'
'  Michael J. Virostko  2016-11-23
'
'  Microsoft Excel has a internal bug that has been around for a while.
'
'  http://www.office-forums.com/threads/scaletype-error.2022608/#post-7430843
'
'  If there is a secondary vertical (Value) axis, then the  primary (Catergory) axis object
'   ActiveChart.Axes(xlCategory, xlPrimary).ScaleType  is <Application-defined or object-defined error>.
'  The values should be either xlScaleLinear or xlScaleLogarithmic.
'  If there is only one vertical axes, you get the correct value.
'  No solution via the web was found, so I wrote this routine to decontruct and recontrust the graph.
'
Dim NSeries As Integer
Dim Sloop As Long
Dim SeriesGroup() As Variant
Dim Y2Axis(1 To 7) As Variant
Dim TestAxis As Boolean
'
'   Determine the number of series
'
  NSeries = ActiveChart.SeriesCollection.Count
  ReDim SeriesGroup(1 To NSeries)
'
'   Set parameter to determine if there is a secondary axis
  TestAxis = False
'
'   Loop through the series to determine if there is a secondary axis
'   If so, then
'    If first series on secondary axis save key parameters for the axis
'       in the array Y2Axis
'    Reset Series to the primary axis
'
  For Sloop = 1 To NSeries
    SeriesGroup(Sloop) = ActiveChart.SeriesCollection(Sloop).AxisGroup
    If SeriesGroup(Sloop) = xlSecondary Then
      If Not TestAxis Then
        With ActiveChart.Axes(xlValue, xlSecondary)
          Y2Axis(1) = .MinimumScale
          Y2Axis(2) = .MaximumScale
          Y2Axis(3) = .MajorUnit
          Y2Axis(4) = .MinorUnit
          Y2Axis(5) = .CrossesAt
          Y2Axis(6) = .ScaleType
          Y2Axis(7) = .TickLabels.NumberFormat
        End With
      End If
      TestAxis = True
      ActiveChart.SeriesCollection(Sloop).AxisGroup = xlPrimary
    End If
  Next Sloop
'
'  Now the chart only has one category and one value axis
'
  ScaleType_Category = ActiveChart.Axes(xlCategory, xlPrimary).ScaleType
'
'   Now recontruct secondary axis
'
  If TestAxis Then
    For Sloop = 1 To NSeries
      If SeriesGroup(Sloop) = xlSecondary Then ActiveChart.SeriesCollection(Sloop).AxisGroup = SeriesGroup(Sloop)
    Next Sloop
    With ActiveChart.Axes(xlValue, xlSecondary)
      .MinimumScale = Y2Axis(1)
      .MaximumScale = Y2Axis(2)
      .MajorUnit = Y2Axis(3)
      .MinorUnit = Y2Axis(4)
      .CrossesAt = Y2Axis(5)
      .ScaleType = Y2Axis(6)
      .TickLabels.NumberFormat = Y2Axis(7)
    End With
  End If

End Function


Thanks for you help in tracking down the root cause of the problem.
I hope this code can help someone else.


Michael Virostko
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Excel VBA secondary axis title
Replies
3
Views
375
NdNoviceHlp
NdNoviceHlp
K
  • Question Question
vba chart unit spacing
Replies
1
Views
164
bobsan42
bobsan42
F
  • Solved
VBA to change Chart fonts on all charts in a sheet
Replies
3
Views
1K
Domenic
D
P
  • Question Question
Turning OFF the visibility of a horizontal (category) axis labels if certain conditions apply
Replies
1
Views
710
NdNoviceHlp
NdNoviceHlp
L
  • Locked
  • Question Question
Help Cleaning Up Code
Replies
2
Views
576
RoryA
RoryA

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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