+ Reply to Thread
Results 1 to 14 of 14

VB Code Scale Y axis

  1. #1
    Registered User
    Join Date
    08-29-2014
    Location
    Preston, England
    MS-Off Ver
    Excel 2013
    Posts
    10

    VB Code Scale Y axis

    Hi all, I'm new to this forum and require a little bit of help if possible.

    I currently have a control chart which I have linked to a worksheet that used a drop down menu to update the values on the Y axis. The drop down menu is linked to a vlookup data table.

    Due to the values on the Y axis going over the % which excel calculates I end up with a Y Axis scale from 0 - 250 (for example)

    I want to write some sort of code that with allow me to scale my Y axis based on the control limits that are returned from the vlookup function.

    I have tried to use the same code as in the attached spreadsheet which I found on another thread, however due to my Max & Min values being returned from a look up function it is not working. I would imagine that I need to include this within the code somehow?

    Any help would be appreciated as I am new to VB Editing.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: VB Code Scale Y axis

    Try, in
    F1 =Max(B2:B250)
    F2 =Min(B2:B250)
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    08-29-2014
    Location
    Preston, England
    MS-Off Ver
    Excel 2013
    Posts
    10

    Re: VB Code Scale Y axis

    Just to confirm that the example I have attached, the VBA works fine.

    I have tried using this same code for my control charts however after referencing the relevant cells in the code it doesn't work when I chose a different product from the drop down menu.

    Could this be due to the cells which update via a VLOOKUP are not included in the VBA code?

  4. #4
    Registered User
    Join Date
    08-29-2014
    Location
    Preston, England
    MS-Off Ver
    Excel 2013
    Posts
    10

    Re: VB Code Scale Y axis

    I have mocked up a simple copy of what I am trying to explain in my post.

    If the spread sheet is opened then in the top left hand corner in YELLOW there is a drop down box which relates to a product selection and this is linked using the V-Lookup function to the data table on the right hand side of the chart. This changed the control limits per product depending upon what is selected.

    When I select the product I always get a chart which starts at 0 and then excel calculates the maximum figure on the Y Axis. I understand if this was one chart relating to one product then I could control the scale of the axis easily.

    What I want to do is for excel to calculate the Y Axis so that the MAXIMUM Y Value (Cell O14) is calculated and linked along with the MINIMUM Y Value (Cell O15). I have included within these cells that I would like the MAX to be +5mm above the maximum pitch, and the minimum to be -5mm below the minimum pitch.

    Due to the scale of the Y axis I would imagine there would also have to be built into the code something in relation to the major/minor units?

    Any help I would be grateful as I have hit a brick wall with this trying various methods.
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: VB Code Scale Y axis

    Unfortunately, your second workbook is significantly different from the first and I don't understand what the chart is supposed to display since there are no data points included.

  6. #6
    Registered User
    Join Date
    08-29-2014
    Location
    Preston, England
    MS-Off Ver
    Excel 2013
    Posts
    10

    Re: VB Code Scale Y axis

    Quote Originally Posted by protonLeah View Post
    Unfortunately, your second workbook is significantly different from the first and I don't understand what the chart is supposed to display since there are no data points included.
    Hi there, sorry I should have been more clear in my post the chart is a control chart for a production process.

    The chart will be issued to production in paper format and then data points calculated & plotted manually. For example for each data point 5 samples are taken & measured then the average is calculated from this and plotted on the chart against Sample 1 (X Axis). The aim for the production process is to keep the process stable within the two orange dashes lines and preferably as close to the green target line as possible.

    The first workbook was an example that I found whilst browsing on the web and I liked the simple nature of the code which was applied to it & was wondering if it was possible to do with my most recent worksheet that I have uploaded.

    The most recent workbook uses a data table and vlookup function to change the chart displayed. However I have tried to apply the code used in the example worksheet and cant seem to get it to work and was wondering if I need to apply some different lines of code due to the different parameters per product.

  7. #7
    Registered User
    Join Date
    08-29-2014
    Location
    Preston, England
    MS-Off Ver
    Excel 2013
    Posts
    10

    Re: VB Code Scale Y axis

    I have added a 'mock up' series of data collected in a table on the right hand side of the attached worksheet and this is plotted onto the chart on the left.

    I have also modified the Y Axis by manually adjusting the max & min scale by formatting the axis manually however I want to create a VBA that automatically does this & adjusts the scale dependant upon what product is selected with the drop down menu.

    Just to explain again from my earlier post, the chart will be issued as a blank chart on paper and the data points will be calculated and plotted by an operator will pen or pencil.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    08-29-2014
    Location
    Preston, England
    MS-Off Ver
    Excel 2013
    Posts
    10

    Re: VB Code Scale Y axis

    Sorry to crash the thread again but I have managed to sort the code which enable the chart to update and reflect the parameters required. However I need to run the macro every time I select a product to enable this change to take place, is there any code or any way in which this could be bypassed?

    Code I have used :

    Sub ChangeAxisScales()
    Dim objCht As ChartObject
    For Each objCht In ActiveSheet.ChartObjects
    With objCht.Chart
    ' Value (Y) Axis
    With .Axes(xlValue)
    .MaximumScale = ActiveSheet.Range("MaxY").Value
    .MinimumScale = ActiveSheet.Range("MinY").Value
    .MajorUnit = ActiveSheet.Range("Unit").Value
    End With
    End With
    Next objCht
    End Sub

  9. #9
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: VB Code Scale Y axis

    This may get you closer (?)

    1. Calculated the maximum value for the six samples in R10:W10
    2. Calculated the minimum value for the six samples in R11:W11
    3. Y-Axis max calculated in R13
    Please Login or Register  to view this content.
    4. Y-Axis min calculated in R14
    Please Login or Register  to view this content.
    5. Changed sample average from linear to column chart
    6. Added sample max and min columns
    7. Moved your formatting code to the worksheet change module:
    Please Login or Register  to view this content.
    8. Slightly changed the chart layout
    Attached Files Attached Files
    Last edited by protonLeah; 09-04-2014 at 12:57 AM.

  10. #10
    Registered User
    Join Date
    08-29-2014
    Location
    Preston, England
    MS-Off Ver
    Excel 2013
    Posts
    10

    Re: VB Code Scale Y axis

    Thank you very much protonLeah, you have solved me one hell of a headache!

    Thanks for being patient too with me being a total novice to this sort of thing.

  11. #11
    Registered User
    Join Date
    08-29-2014
    Location
    Preston, England
    MS-Off Ver
    Excel 2013
    Posts
    10

    Re: VB Code Scale Y axis

    I can modify my current worksheets to suit the code given.

    One final question, what would I change the code to if I have 2 or more charts within the worksheet tab? I have labelled each chart as :
    Chart A, Chart B, Chart C.......

    I would have to reference them within the code in order for the correct chart to update?

  12. #12
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: VB Code Scale Y axis

    You can refer to the charts by name, but you would have post a workbook showing which data is linked to which chart and what would trigger the update.

  13. #13
    Registered User
    Join Date
    08-29-2014
    Location
    Preston, England
    MS-Off Ver
    Excel 2013
    Posts
    10

    Re: VB Code Scale Y axis

    I have included the worksheet which you modified and added another chart which I want to display alongside the existing one.

    Included in the worksheet all data which I've added is in Bold black, Italic and in a Black bordered box.

    As you can see when the worksheet is opened up the current VBA code modifies both charts to suit the code, however I want to modify the code somehow so that the code refers to each chart individually. E.g "Chart 1" "Chart 2" & maybe even refer to the sheet as "Sheet 1"

    I have included a new data table below the existing one which is used to update the Range chart dependant on the product selected.

    Again there are no data points for the Range chart due to the chart being printed off onto paper and the data points being plotted by hand.
    The range is calculated by subtracting the minimum captured data point from the maximum data captured from 5 the Samples taken. This is then plotted onto the range chart.

    Once I have a code which works for this then I can apply to various other worksheets providing it is possible to incorporate the worksheet name and chart names within the VBA code. ( I really need to read a book or do more research into this)

    Thanks again in advance if anyone can help me!
    Attached Files Attached Files

  14. #14
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: VB Code Scale Y axis

    I don't understand your added data, the numbers don't seem to relate to the random numbers I used, but I have modified the code to plot it in chart 2. I also added a chart 3 if that's what you were intending. Here's the latest code:
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by protonLeah; 09-08-2014 at 03:48 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Secondary axis not picking up amended data for axis scale
    By AileenR in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 10-02-2013, 06:17 AM
  2. Replies: 0
    Last Post: 08-09-2006, 08:54 PM
  3. Replies: 0
    Last Post: 08-09-2006, 08:49 PM
  4. [SOLVED] How to make Primary axis and Secondary X-axis have the same scale
    By AdamCPTD in forum Excel General
    Replies: 0
    Last Post: 07-14-2006, 09:15 AM
  5. [SOLVED] How to insert X axis scale values next to axis and X axis grid lin
    By vp23larry in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 06-23-2005, 11:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1