Setting the Max Scale on a chart in Access VBA

[Grd]

Hi,

I have a chart on my report in the group header that repeats for several
groups. I'd like to set the maximum scale on the chart through VBA so it is
consistent accross all the charts. (I don't want to set it manually, which is
straight forward to do in the design of the chart because the maximum scale
is going to be dynamic. I will determine this in advance using a query that
will determine the number to be used for the maximum scale.)

I figure I need to do this in VBA. Could someone help with the code as I'm
struggling with this one.

Any help is greatly appreciated.

Thanks in advance

Sandra

 

[Alan Z. Scharf]

Sandrs,

Settings like that have to be made in design view, not at run time.

Hence, the trick is to do it in VBA in report design mode, invisible to the
user interface.

The key is to open the report in acViewDesign.
Below is a code example of setting other MSChart items in design mode.

This would be called before executing the actual print command in VBA. It
takes a split second.


Alan


Public Sub SetScalesGrowthOfThousand(strReportName As String, sngYears As
Single)
' Set units in design mode based on no. of years
DoCmd.Echo False
DoCmd.OpenReport strReportName, acViewDesign

With
Reports!rptMultiManagerpage2![chtGrowthofThousand].Object.Application.Chart.
Axes(1)
Select Case sngYears
Case 0 To 0.5
' Up to 6 months - show monthly
.baseunit = xlmonths
.MajorUnit = 1
.MajorUnitScale = xlmonths
Case 0.51 To 1#
' Between 6 months and 1 year - show every two months
.baseunit = xlmonths
.MajorUnit = 2
.MajorUnitScale = xlmonths
Case 1.01 To 6
' Between 1 and six years - show yearly
.baseunit = xlmonths
.MajorUnit = 1
.MajorUnitScale = xlYears
End Select
End With

' Save revised design
DoCmd.Close acReport, "rptMultiManagerPage2", acSaveYes
DoCmd.Echo True

End Sub

 

[Grd]

Thanks Alan,

This looks like the kind of thing I'm after.

Regards
S

Sandrs,

Settings like that have to be made in design view, not at run time.

Hence, the trick is to do it in VBA in report design mode, invisible to the
user interface.

The key is to open the report in acViewDesign.
Below is a code example of setting other MSChart items in design mode.

This would be called before executing the actual print command in VBA. It
takes a split second.


Alan


Public Sub SetScalesGrowthOfThousand(strReportName As String, sngYears As
Single)
' Set units in design mode based on no. of years
DoCmd.Echo False
DoCmd.OpenReport strReportName, acViewDesign

With
Reports!rptMultiManagerpage2![chtGrowthofThousand].Object.Application.Chart.
Axes(1)
Select Case sngYears
Case 0 To 0.5
' Up to 6 months - show monthly
.baseunit = xlmonths
.MajorUnit = 1
.MajorUnitScale = xlmonths
Case 0.51 To 1#
' Between 6 months and 1 year - show every two months
.baseunit = xlmonths
.MajorUnit = 2
.MajorUnitScale = xlmonths
Case 1.01 To 6
' Between 1 and six years - show yearly
.baseunit = xlmonths
.MajorUnit = 1
.MajorUnitScale = xlYears
End Select
End With

' Save revised design
DoCmd.Close acReport, "rptMultiManagerPage2", acSaveYes
DoCmd.Echo True

End Sub

Hi,

I have a chart on my report in the group header that repeats for several
groups. I'd like to set the maximum scale on the chart through VBA so it is
consistent accross all the charts. (I don't want to set it manually, which is
straight forward to do in the design of the chart because the maximum scale
is going to be dynamic. I will determine this in advance using a query that
will determine the number to be used for the maximum scale.)

I figure I need to do this in VBA. Could someone help with the code as I'm
struggling with this one.

Any help is greatly appreciated.

Thanks in advance

Sandra

 

[Duane Hookom]

I believe you can do this with code in the On Format event of the report. I
usually create a similar chart in Excel. Then turn on the macro recorder and
make the changes to the Max Scale in the chart. Stop the macro recording and
copy the code into your report event. You can make a few changes to the code
and run it in Access.

--
Duane Hookom
MS Access MVP

Thanks Alan,

This looks like the kind of thing I'm after.

Regards
S

Sandrs,

Settings like that have to be made in design view, not at run time.

Hence, the trick is to do it in VBA in report design mode, invisible to
the
user interface.

The key is to open the report in acViewDesign.
Below is a code example of setting other MSChart items in design mode.

This would be called before executing the actual print command in VBA.
It
takes a split second.


Alan


Public Sub SetScalesGrowthOfThousand(strReportName As String, sngYears As
Single)
' Set units in design mode based on no. of years
DoCmd.Echo False
DoCmd.OpenReport strReportName, acViewDesign

With
Reports!rptMultiManagerpage2![chtGrowthofThousand].Object.Application.Chart.
Axes(1)
Select Case sngYears
Case 0 To 0.5
' Up to 6 months - show monthly
.baseunit = xlmonths
.MajorUnit = 1
.MajorUnitScale = xlmonths
Case 0.51 To 1#
' Between 6 months and 1 year - show every two months
.baseunit = xlmonths
.MajorUnit = 2
.MajorUnitScale = xlmonths
Case 1.01 To 6
' Between 1 and six years - show yearly
.baseunit = xlmonths
.MajorUnit = 1
.MajorUnitScale = xlYears
End Select
End With

' Save revised design
DoCmd.Close acReport, "rptMultiManagerPage2", acSaveYes
DoCmd.Echo True

End Sub

Hi,

I have a chart on my report in the group header that repeats for
several
groups. I'd like to set the maximum scale on the chart through VBA so
it is
consistent accross all the charts. (I don't want to set it manually,
which is
straight forward to do in the design of the chart because the maximum scale
is going to be dynamic. I will determine this in advance using a query that
will determine the number to be used for the maximum scale.)

I figure I need to do this in VBA. Could someone help with the code as
I'm
struggling with this one.

Any help is greatly appreciated.

Thanks in advance

Sandra