Scaling Graphs

[Steen]

Hi

Can anyone help me gette the below code working - novice VB programmer

I want the scale of Graph 1 and 2 to change each time the value in row 2 and
col 4 changes. Min and Max scacle ís set in i2 and i4 when changing the above
value.

Any help would be appriciated.

'Private Sub Worksheet_Change(ByVal Target As Range)
'Dim StartDate
'Dim StopDate
'
' If Target.Row = 2 And Target.Column = 4 Then
' StartDate = Range(i2)
' StopDate = Range(i4)
'
' With ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlCategory)
' .MinimumScale = StartDate - 7
' .MaximumScale = StopDate +7
' End With
'
' ' With ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlCategory)
' ' .MinimumScale = StartDate - 7
' ' .MaximumScale = StopDate + 7
' 'End With
'
' End If
'End Sub

 

[Jon Peltier]

You didn't hint at where the code stops, or what error messages you saw.

One thing I noticed: Change this (and related statements):

StartDate = Range(i2)

to

StartDate = Range("i2").Value

- Jon

 

[Steen]

Hi Jon

That did the job - shame on me for not seeing this my self

All how this just lead to the next problem - the change of scale didn't work
like I supposed. The timescale is changed allright, but some of the
information (vertical deadlines and budget lines) get messed up, when
changing view :-(

It's a bit difficult to explain but thanks for the help so far.

/steen

 

[Jon Peltier]

How are these other features added to the chart?

- Jon

 

[Steen]

Hi Jon

Thanks for coming back on the issue - I'm quite busy at the moment but I
will try to make an explanation during the weekend.

/Steen

 

[Steen]

Hi again

I've been looking a little further on the problem. It seem as the changing
of scale on x-axs i ok, but my budget lines and deadlines don't follow this
change. These lines has been added using the following link:
http://peltiertech.com/Excel/Charts/AddLineVertSeries.html

The problem might have somthing to do with the use of a secondary axis...

Can anyone help me out hire?

/Steen

 

[Jon Peltier]

You have to alter the X values of the added series to make their scale
compatible with the existing category or time scale axis, or coordinate a
secondary X axis if that's what you're using.

- Jon

 

[Steen]

Hi Jon

Thanks for the quick reply, any help on how to do this - I'm no superman in
making VB scripts?

/Steen

 

[Jon Peltier]

Who mentioned anything about VBA? It's a matter of lining up the data.

What does the data for the time series look like, and the data for the added
series?

- Jon

 

[Steen]

Hi again Jon

Sorry for been "out" for a long time - I have been very busy and are looking
forwart to a waction in week 11.

The reason for the VB scripting issue is that I want to be able to "zoom"
the graph(s) at specific date intervals and back again. I have tried to do
this with the script below and i almost work, besides the delines and budget
lines I have inserted.

Data used in the graps are quite simple:

Date - X: %Finished - Y:
2007-01-01 0
2007-01-02 2
...... ....
2007-01-28 100

Deadline - X: Value - Sec. Y
2007-01-26 0
2007-01-26 100

I also have some budget lines and they are basical done the same way as the
deadlines.

/Steen