• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro code for fixing char axis value

D1gg13r

New Member
Hi All,

I'm not even a novice with VB let alone applying it to Excel, however I need to apply this to a Macro button to resolve an issue I have.
What I need to do is be able to set the maximum value of the X axis within the attached chart that resides in the 'Dashboard' tab. This should essentially reflect the value of cell D7 in the 'Summary' tab.

What I was hoping would work to do this is add a macro button (which I've added in cell D6 of the 'Dashboard' tab) that essentially sets the axis value for the stacked bar chart under it to the value of whatever is entered in D7 of the 'Summary' tab.

The code I have against this macro right now (that is not working) is as follows:

Code:
Sub Set_Axis_Value()
'
' Set_Axis_Value Macro
' Sets the value to match the allocated budget
'

'
    ActiveSheet.ChartObjects("Chart 21").Activate
    ActiveSheet.ChartObjects("Chart 21").Activate
    ActiveChart.Axes(xlValue).Select
    Sheets("Summary").Select
    Range("D7").Select
    Sub ScaleAxes()
  With ActiveChart.Axes(xlCategory, xlPrimary)
    .MaximumScale = Sheet3.Range("D7").Select
    .MinimumScale = Range("D71").Select
  End With
  With ActiveChart.Axes(xlValue, xlPrimary)
    .MaximumScale = Sheet3.Range("D7").Select
    .MinimumScale = Range("D71").Select
  End With
  
End Sub

Any assistance would be greatly appreciated.

Thank you,
D1g.
 

Attachments

  • Budget Template_sample data.xlsm
    54.6 KB · Views: 1
Last edited by a moderator:
D1g

Firstly, welcome to the Chandoo.org Forums

Your code should be:

Code:
Sub Set_Axis_Value()
'
' Set_Axis_Value Macro
' Sets the value to match the allocated budget
'

  Worksheets("Dashboard").Select

  ActiveSheet.ChartObjects("Chart 21").Activate

  With ActiveChart.Axes(xlValue, xlPrimary)
  .MaximumScale = Worksheets("Summary").Range("D7")
  .MinimumScale = Worksheets("Summary").Range("D71")
  End With

End Sub

or

Code:
Sub Set_Axis_Value()
'
' Set_Axis_Value Macro
' Sets the value to match the allocated budget
'

  Sheet2.Select
  
  ActiveSheet.ChartObjects("Chart 21").Activate
  'ActiveChart.Axes(xlValue).Select

  With ActiveChart.Axes(xlValue, xlPrimary)
  .MaximumScale = Sheet3.Range("D7")
  .MinimumScale = Sheet3.Range("D71")
  End With
  
End Sub
 
Back
Top