dhananjaywalke
Board Regular
- Joined
- Jun 26, 2006
- Messages
- 60
Hi,
I have set of information which i present in the graph format (such as Revene, Profit, Return on Capital%..etc..). I have a list of accounts to select. Once i select the account graph data for that account gets automatically selected through lookup formulae.
I have also created a macro to adjust scale & format of labels & X axis labels as below.
Sub Update_Scale()
application.ScreenUpdating = False
minscale = Range("MinScale").Value
MaxScale = Range("MaxScale").Value
minscale1 = Range("MinScale1").Value
MaxScale1 = Range("MaxScale1").Value
FormatValue = Range("FormatValue").Value
Sheets("Graphs").Select
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.Axes(xlValue).Select
selection.TickLabels.NumberFormat = FormatValue
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).DataLabels.Select
selection.NumberFormat = FormatValue
ActiveChart.SeriesCollection(2).DataLabels.Select
selection.NumberFormat = FormatValue
With ActiveChart.Axes(xlValue)
.MinimumScale = minscale
.MaximumScale = MaxScale
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.Axes(xlValue).Select
selection.TickLabels.NumberFormat = FormatValue
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).DataLabels.Select
selection.NumberFormat = FormatValue
ActiveChart.SeriesCollection(2).DataLabels.Select
selection.NumberFormat = FormatValue
With ActiveChart.Axes(xlValue)
.MinimumScale = minscale1
.MaximumScale = MaxScale1
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
Range("A1").Select
End Sub
Sub Update_format()
application.ScreenUpdating = False
FormatValue = Range("FormatValue").Value
Sheets("Graphs").Select
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.Axes(xlValue).Select
selection.TickLabels.NumberFormat = FormatValue
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).DataLabels.Select
selection.NumberFormat = "0;(0);"
selection.NumberFormat = FormatValue
ActiveChart.SeriesCollection(2).DataLabels.Select
selection.NumberFormat = "0;(0);"
selection.NumberFormat = FormatValue
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.Axes(xlValue).Select
selection.TickLabels.NumberFormat = FormatValue
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).DataLabels.Select
selection.NumberFormat = "0;(0);"
selection.NumberFormat = FormatValue
ActiveChart.SeriesCollection(2).DataLabels.Select
selection.NumberFormat = "0;(0);"
selection.NumberFormat = FormatValue
Range("A1").Select
End Sub
_____________________
Now the problem is when i select account with million # format & then account with % format. The lable format does not change. It changes if i again choose some account with format similar to earlier account.
Is there any issue with my macro.
Please help!
[/code]
I have set of information which i present in the graph format (such as Revene, Profit, Return on Capital%..etc..). I have a list of accounts to select. Once i select the account graph data for that account gets automatically selected through lookup formulae.
I have also created a macro to adjust scale & format of labels & X axis labels as below.
Sub Update_Scale()
application.ScreenUpdating = False
minscale = Range("MinScale").Value
MaxScale = Range("MaxScale").Value
minscale1 = Range("MinScale1").Value
MaxScale1 = Range("MaxScale1").Value
FormatValue = Range("FormatValue").Value
Sheets("Graphs").Select
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.Axes(xlValue).Select
selection.TickLabels.NumberFormat = FormatValue
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).DataLabels.Select
selection.NumberFormat = FormatValue
ActiveChart.SeriesCollection(2).DataLabels.Select
selection.NumberFormat = FormatValue
With ActiveChart.Axes(xlValue)
.MinimumScale = minscale
.MaximumScale = MaxScale
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.Axes(xlValue).Select
selection.TickLabels.NumberFormat = FormatValue
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).DataLabels.Select
selection.NumberFormat = FormatValue
ActiveChart.SeriesCollection(2).DataLabels.Select
selection.NumberFormat = FormatValue
With ActiveChart.Axes(xlValue)
.MinimumScale = minscale1
.MaximumScale = MaxScale1
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
Range("A1").Select
End Sub
Sub Update_format()
application.ScreenUpdating = False
FormatValue = Range("FormatValue").Value
Sheets("Graphs").Select
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.Axes(xlValue).Select
selection.TickLabels.NumberFormat = FormatValue
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).DataLabels.Select
selection.NumberFormat = "0;(0);"
selection.NumberFormat = FormatValue
ActiveChart.SeriesCollection(2).DataLabels.Select
selection.NumberFormat = "0;(0);"
selection.NumberFormat = FormatValue
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.Axes(xlValue).Select
selection.TickLabels.NumberFormat = FormatValue
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).DataLabels.Select
selection.NumberFormat = "0;(0);"
selection.NumberFormat = FormatValue
ActiveChart.SeriesCollection(2).DataLabels.Select
selection.NumberFormat = "0;(0);"
selection.NumberFormat = FormatValue
Range("A1").Select
End Sub
_____________________
Now the problem is when i select account with million # format & then account with % format. The lable format does not change. It changes if i again choose some account with format similar to earlier account.
Is there any issue with my macro.
Please help!
[/code]