Error 424 when setting chart axis values

Hello.
I have this code to export a query to excel and create a chart.
The code is working fine:

Please Login or Register  to view this content.

However, when I type beneat this code to set set maximun and minimum axis carth values

Please Login or Register  to view this content.

the following line always there is the Object required error '424':

Please Login or Register  to view this content.

I hope someone can help,

All the best

Hello.

It's been over three days with no more replies.
I am still working this on my own, but, I will still appreciate any help to fix my problem.

Thanks in advance!

Andy. I really appreciate your quick reply.
I'm going to test your suggestion and I'll let you know about it.
Cheers

Andy Pope. Again I appreciate your help.

I tested your suggestion within "With ch" in my code (below) and again I failed: "ERROR 424 and object is required" in line:

Please Login or Register  to view this content.

I think I don't know how to reference it.

Let me know about my project.

In Access I have a Form with 2 textboxes and one command button.
In txttask_plot, user types plot value (numeric)
In txttask_from, the user selects day1 (Date)
In txttask_to, the user selects day1 (Date)
The command button runs a code that works fine exporting the query and creating the Chart

This is my code in my command button:

Please Login or Register  to view this content.

What I'm looking for is for VBA to execute the following:
Read range B2(to the last row) in the query minimum and maximum values to set values range (or escale?) in the primary vertical axis
Read range C2(to the last row) in the query minimum and maximum values to set values range (or escale?) in the secondary vertical axis
Therefore, any time I export a query to Excel VBA will set the minimum and maximum values in Primary and Secondary Axes values

I have been googling for the last 3 days to find a solution with no success.

I really appreciate your help.

Cheers

Andy Pope. Many thanks.
I am running my code from MS Access.

This is my code form MS Access:

Please Login or Register  to view this content.

However, I have been unable to set minimun and maximun values in primary and secondary axes in my chart and I am still working.

On the other hand, the excel file attached is very useful and educational to create graphics.
I appreciate your valuable help.

Andy Pope.
Since 01/09/2018 I am studying how to use early binding and late binding.
In MS Acess VBA Editor I checked Microsoft Excel 16.0 Object Library
In MS Excel VBA Editor I checked Microsoft Access 16.0 Object Library

For the late binding I found the information on this links:
https://www.devhut.net/2012/04/19/ms...ords-to-excel/
http://www.granite.ab.ca/access/latebinding.htm
http://www.dyalog.com/uploads/confer...ndexamples.htm

I am a newbie on this respect.

Now my code exports to excel the query and creates the chart with:
Primary and secondary axis values
Labels values in the axis
Chart Background Colour

The code I used is almost the same in MS Excel.
So far, I could not find the instructions to set the minimum and maximum values for the primary axis and for the secondary axis.

In Excel, your code works well to set the minimum and maximum values of the primary axis and the secondary axis.
My problem is that I have not been able to adjust your lines of code to Access VBA.

Please, could you tell me which website I can visit to find the solution to my problem?
Cheers

Andy Pope. I really appreciate your reply.
The below code has lines ('Set maximum and minimum axis values) to set minimum and maximum axis values from the query.

Please Login or Register  to view this content.

However, in the chart:
Primary axis minimum value starts at 0.
Primary axis maximum value is not query value.
Secondary axis minimum value starts at 0.
Secondary axis maximum value is not query value.

Many thanks in advanced

Andy Pope.
I understood that your code is for Excel. Your code works excellent in Excel.
I really apologize for omitting your code in my previous posts.

I have been trying to write your VBA Excel code within my code in MS Access VBA

Please Login or Register  to view this content.

But any time I click th command dbutton VBA coloured in blue the underlined words:
.MinimumScale = Application.WorksheetFunction.Min(dataValuesPrimary)
and displays this message: "MS Access VBA Compilation Error Method or data member not found."

I used Dim ch As Object ''Excel.Chart instead of Dim cht As Chart and VBA displays same error
I used Dim cht As Chart instead of Dim ch As Object ''Excel.Chartand VBA displays same error

I have been googling on how to fix such error with no success.

Again I really appreciate your help.
Cheers

Andy Pope.
Thank you for your time and effort in helping me.

I tested you excel code in excel and works perfectly.
I'm worried because I have been unable to run in Access the code you provided.
The attached image shows in yellow 'Error, 438 Object doesn't support this property or method.' error438_code_export_qry_chart_primary_secondary_axis.jpg. I can provide the code if needed.

Although I have been studying about early binding and late binding I still do not know which code lines indicate if it is early binding or late binding.
I am completely stumped on a VBA setting vertical axis min and max values.:confused.

Cheers

Andy Pope.

Those two lines of code have kept me with a headache for a week
Now my code runs.
Thank you very much for your help.