VBA code to automatically set X-Axis on scatter chart

[MitzDriver]

I've tried everything that I know of (which is not much) and still can't get what I want. Actually have worked on this for almost a year (off and on).

I would like VBA code to set the minimum and maximum x-axis for a scatter chart named chart1. Which is the ONLY chart in the only worksheet in the only workbook.

I would like to use the minimum and maximum value in a38:a100 which is named dbyear.

If I have to use a button to call the macro fine. However, I would rather it work automatically anytime I change a value (a four digit year) in the dbyear range.

Thanks in advance and all help will be greatly appreciated.

 

[AFPathfinder]

This might be in the ballpark of what your asking for. I don't have much experience altering charts with VBA.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Application.Intersect(Target, Range("dbyear")) Then
        ActiveSheet.Shapes("Chart 1").Axes(xlCategory).MinimumScale = WorksheetFunction.Min(Range("dbyear").Address)
        ActiveSheet.Shapes("Chart 1").Axes(xlCategory).MaximumScale = WorksheetFunction.Max(Range("dbyear").Address)
    End If
End Sub

This would go in your worksheet object and should activate whenever there is a change in that sheet. The If statement will check to see if the change occurred within the dbyear range. If true, it should set the minimum x-axis value using the Min function and the maximum to the Max function all using the dbyear range. Let me know if something else is needed.

 

[MitzDriver]

Thanks for taking a look at this and trying to help.

I have inserted it into the visual basic but not sure I put it in the right place. It is not working. As in not doing anything.

If you could explain what I'm doing wrong and help further I would really appreciate it.


If it helps here is a link to my onedrive where the file is located. https://tinyurl.com/ycascb8m

Thanks again

 

[AFPathfinder]

It could be that the code doesn't work or maybe dbyear isn't being referenced correctly. I'll give it a look over when I get home from work (can't download unknown files on this network).

 

[AFPathfinder]

I don't have access to your sharepoint, so I'm not able to view the file. I would place a break on the If statement and then trigger a change on the sheet to see if it is at least executing. If it is and the If statement is coming up false, may need to do some work in the immediate window to figure out what the Target value is and what the dbyear range is.

 

[MitzDriver]

Good afternoon,

I just wanted to say thinks for taking a look at this.

I was not able to get your formula to work (my lack of knowledge of vba), but was able to get a macro set up that does what i'm after.

[FONT=Calibri][SIZE=3][COLOR=#000000]Sub AdjustChart()[/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]'
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]' Adjust the Chart X-Axis to fit the date range[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   ActiveSheet.ChartObjects("Chart1").Activate[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]  ActiveSheet.ChartObjects("Chart1").Activate
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   ActiveChart.Axes(xlCategory).Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   ActiveChart.Axes(xlCategory).MinimumScale = Range("k37").Value[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   ActiveChart.Axes(xlCategory).MaximumScale = Range("k38").Value
[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   Application.CommandBars("Format Object").Visible = False[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Range("B39").Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]

Not exactly what I wanted but will do.

Below is a link you should be able to look at (not sharepoint).

Thanks again for you help

https://1drv.ms/x/s!AgFU_qRLWcZBg1RvZlKDR5eZzGDB

 

[AFPathfinder]

You're welcome!

 

[Jon Peltier]

I wrote a tutorial that shows how to link a chart's axis scale to cells in the worksheet: Link Excel Chart Axis Scale to Values in Cells.

Since using the actual min and max from the data aren't usually the best min and max for the axes, I wrote another tutorial that has algorithms to calculate nice axis limits that encompass the data min and max: Calculate Nice Axis Scales in Your Excel Worksheet.

The first tutorial includes measures that make it dynamic, either Worksheet_Change for when the data itself changes, or (in the comments below the tutorial) Worksheet_Calculate, for when new axis limits are calculated.