chart problem

    Hi,
    Is it possible to automaticaly set the Y-value scale of a chart?
    A user has to enter values but I don't know what the min and the max of the value is. It is possible to set the min and the max value of the Y-axis to auto in the format axis. But when I set the min value to auto excel always use 0 as the min value. This makes the chart unreadable because the Y-axis becomes to big.
    Is it possible to set the min. value to ie 1 less than the min value the user entered?


    Thanx,
    Fluppe

    The min value of the y value of a chart can easily set with a macro. How do you want it specified? There can be a scroll bar control, a cell reference, or whatever. Where is the user entering the data? If you care to say how you want it done, I'll be happy to create a demo for you.

    Hi Derk,
    The user is entering data in columns. There are ie 3 colums with data, each column is a serie (y-value).
    The x-value contains the datums when they entered the data.
    The values are between ie 10 and 60. When the max value is ie 20 and the min value is 10 I want the max value displayed on the chart 1 more than the max value (=21) and the min value 1 less than the min value (=9). Is it possible that excel does it automatically?
    If not can you give me a demo how you would do it, or do you need more info?


    Thank you very much,
    Fluppe

    Try this example. I defined the y values in the worksheet with the name Data. Whenever one of these values is changed the macro attached to the sheet (right click on the sheet tab and view code) will reset the minimum and maximum scakes of the chart. Ifthere were more than one chart, then the code would need adjusting. Does this get you started?

    Hi,
    This chart works great while the range and the chart are on the same sheet.
    What do I have to change in the code when the range is on sheet1 and the chart on sheet2?
    Is this still possible?


    Thanx,
    Fluppe:wink1:

    Hi,
    I don't know what I have to change in the code to make it usefull for more then 1 chart in sheet2?
    I'm using 4 ranges. Each range has a chart. The ranges are in sheet1.
    Is someone willing to help me?


    Thanx,
    Fluppe

    Hi Andy,
    I'm using the code that Derk and you gave me:
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim r As Range
    Set r = Range("metingen1")

    If Intersect(r, Target) Is Nothing Then Exit Sub
    With Worksheets("grafiek").ChartObjects(1).Chart.Axes(xlValue)
    .MinimumScale = Int(Application.Min(r)) - 0.5 'Int returns integer portion of number.
    .MaximumScale = Int(Application.Max(r)) + 0.5
    End With

    End Sub


    I only tried to Set another range
    ie Set s as range. But that is not the way it works.

    You need to change the ChartObjects reference to use the correct chart.


    One way would be to test all four ranges


    Code
    For intIndex 1 to 4
    Set r = Range("metingen" & cstr(intIndex)) 
    If not Intersect(r, Target) Is Nothing Then    
       With Worksheets("grafiek").ChartObjects(intIndex).Chart.Axes(xlValue) 
           .MinimumScale = Int(Application.Min(r)) - 0.5 'Int returns integer portion of  number. 
          .MaximumScale = Int(Application.Max(r)) + 0.5 
        End With 
        Exit For
    End if
next


    This assumes the chartobjects are indexed in the same order and the ranges.


    Another option would be to name the chartobjects themselves.
    Hold the shift key whilst selecting the chart. You can then use the Name box (let of formula bar) to enter a name.


    eg:
    With Worksheets("grafiek").ChartObjects("MyChart" & cstr(intIndex)).Chart.Axes(xlValue)


    Cheers
    Andy

    [h4]Cheers
    Andy    [/h4]

    Hi Andy,
    I tried the code you posted here but it doesn't seem to work.
    I defined a second range ie "metingen2" in sheet 1 for my second chart in sheet2.
    Only the first chart is working ("metingen1"). I don't know what went wrong.


    I will try to name the chartobjects themselves.


    thanx

    Hi Andy,
    the following code was the problem in my sheet:
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    This must be:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range) like you used.


    Thanx,
    Fluppe

    Hi,
    The user can enter data in the range by using a userform.
    But now, when I use code for adjusting the y-axis the process of transporting the values in the textboxes to the cells is going much slower.
    Is there something we can do about it?
    thanx,
    Filip

    Hi Fluppe,


    I should imagine that every time you write a value into a cell the routine is checking whether or not to update one of the charts. This would indeed slow you down.


    If you are using a userform do you still need the macro connected to the worksheet event?


    If you are writing the values to cells one at a time how about filling the range in one go.


    Cheers
    Andy

    [h4]Cheers
    Andy    [/h4]

    Hi Andy,
    I only need the macro when I want to show the sheet where the charts are stored.


    What do you mean with "filling the range in one go"?


    thanx,
    Fluppe

    If you store the values you want to write to the sheet in an array, you can write the whole array in one statement.
    And if the macro is inside the Change event this will only be executed once.



    But if you only want to update the chart when the user has finished entering values then you should execute the macro from the userform. Either by pressing a button or when the useform terminates. Also move the code into a module or a routine in the userform.


    Cheers
    Andy

    [h4]Cheers
    Andy    [/h4]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login