excel charts resizing bounds and display mouse position details

[mhwolog]

Hello, I'm having a bit of trouble achieving two things with my excel chart.
This is the chart:

I want to set some cells in the worksheet so that I can enter the minimum and maximum bounds of the X and Y axis for the chart, without having to go to the format plot area options. I have found the below code which I can use with this command:
=setChartaxis("Sheet12", "Chart 2", "Max", "Category", "Primary", AB55)

Function setChartAxis(sheetName As String, chartName As String, MinOrMax As String, _
    ValueOrCategory As String, PrimaryOrSecondary As String, Value As Variant)


'Recalculate the formula every time
Application.Volatile


'create variables
Dim cht As Chart
Dim valueAsText As String


'Set the chart to be controlled by the function
Set cht = Application.Caller.Parent.Parent.Sheets(sheetName) _
    .ChartObjects(chartName).Chart


'Set Value of Primary axis
If (ValueOrCategory = "Value" Or ValueOrCategory = "Y") _
    And PrimaryOrSecondary = "Primary" Then


    With cht.Axes(xlValue, xlPrimary)
        If IsNumeric(Value) = True Then
            If MinOrMax = "Max" Then .MaximumScale = Value
            If MinOrMax = "Min" Then .MinimumScale = Value
        Else
            If MinOrMax = "Max" Then .MaximumScaleIsAuto = True
            If MinOrMax = "Min" Then .MinimumScaleIsAuto = True
        End If
    End With
End If


'Set Category of Primary axis
If (ValueOrCategory = "Category" Or ValueOrCategory = "X") _
    And PrimaryOrSecondary = "Primary" Then


    With cht.Axes(xlCategory, xlPrimary)
        If IsNumeric(Value) = True Then
            If MinOrMax = "Max" Then .MaximumScale = Value
            If MinOrMax = "Min" Then .MinimumScale = Value
        Else
            If MinOrMax = "Max" Then .MaximumScaleIsAuto = True
            If MinOrMax = "Min" Then .MinimumScaleIsAuto = True
        End If
    End With
End If


'Set value of secondary axis
If (ValueOrCategory = "Value" Or ValueOrCategory = "Y") _
    And PrimaryOrSecondary = "Secondary" Then


    With cht.Axes(xlValue, xlSecondary)
        If IsNumeric(Value) = True Then
            If MinOrMax = "Max" Then .MaximumScale = Value
            If MinOrMax = "Min" Then .MinimumScale = Value
        Else
            If MinOrMax = "Max" Then .MaximumScaleIsAuto = True
            If MinOrMax = "Min" Then .MinimumScaleIsAuto = True
        End If
    End With
End If


'Set category of secondary axis
If (ValueOrCategory = "Category" Or ValueOrCategory = "X") _
    And PrimaryOrSecondary = "Secondary" Then
    With cht.Axes(xlCategory, xlSecondary)
        If IsNumeric(Value) = True Then
            If MinOrMax = "Max" Then .MaximumScale = Value
            If MinOrMax = "Min" Then .MinimumScale = Value
        Else
            If MinOrMax = "Max" Then .MaximumScaleIsAuto = True
            If MinOrMax = "Min" Then .MinimumScaleIsAuto = True
        End If
    End With
End If


'If is text always display "Auto"
If IsNumeric(Value) Then valueAsText = Value Else valueAsText = "Auto"


'Output a text string to indicate the value
setChartAxis = ValueOrCategory & " " & PrimaryOrSecondary & " " _
    & MinOrMax & ": " & valueAsText


End Function

But it seems to change the axis options>units major instead of the axis options> bounds > minimum and maximum (which I want to change).
How do I alter the code or the worksheet formula to achieve this?

And this code is currently in a module. I use a template worksheet with this chart and copy it each time I set up a new page. This results in different sheet numbers (sheet 11, sheet 12 etc), but on each one the chart is named chart 2. If I want to have these cells/fields to alter the x and y dimensions of the chart on each separate worksheet - and want it to only affect the chart on that worksheet -> do I have the code in the correct spot? or should it be located in VBA under the template worksheet? And will the formula on the template worksheet automatically alter the sheet number if I create copies of the template?


The other thing I would like to achieve is to be able to hover my mouse over the chart, and the get resulting X, Y values/coordinates either in a popup or field + a vertical line to display (as seen in the below image). It would look like this:

Thank you so much for your help.

 

[mhwolog]

Sorry I'm not sure how to post a picture, my posting permissions say I may not post attachments.

 

[mhwolog]

[URL=http://s1253.photobucket.com/user/leonwij/media/chart_zpsjsy56qak.jpg.html] Picture of my chart 1

Picture of chart 2 - showing what I want to achieve

 

[mhwolog]

Can anyone help with this?

 

[mhwolog]

Hi All,
If anyone is interested... this is a link that I found to resize the chart bounds/min/max
https://peltiertech.com/link-excel-chart-axis-scale-to-values-in-cells/

I am still trying to work out the mouse hovering.