+ Reply to Thread
Results 1 to 5 of 5

Slider/cell value to change x-axis range

  1. #1
    Registered User
    Join Date
    09-10-2010
    Location
    UK, Marlborough
    MS-Off Ver
    365 (latest beta)
    Posts
    57

    Slider/cell value to change x-axis range

    Hi,

    I have a charted table of rates (y:£) vs. quantities (x:m2) with a number of slicers which is mostly perfect. However I need to allow people to change the range of the x-axis as there are a few exponentially high quantities which need to be included, but not necessarily viewed as we need to concentrate on certain ranges of quantities.

    Ideally I would like a slider to change the x-axis bounds - is there an easy way to do this?

    Thanks,

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Slider/cell value to change x-axis range

    Sure - you can use the worksheet's calculate event. Link the slider to a cell - say, D3 - and in another cell, link to that cell using =D3 (this is to tie into the calculate event). Then copy this code, right-click the sheet tab, select "View Code" and paste the code into the window that appears. Then save it as a macro-enabled .xlsm

    (Change Chart 4 to the name of the chart of interest.)

    Private Sub Worksheet_Calculate()
    Me.ChartObjects("Chart 4").Chart.Axes(xlCategory).MaximumScale = Me.Range("$D$3").Value
    End Sub


    You can add a line if you want to change the min scale, too, but that needs another slider/cell/link.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    09-10-2010
    Location
    UK, Marlborough
    MS-Off Ver
    365 (latest beta)
    Posts
    57

    Re: Slider/cell value to change x-axis range

    Great, thanks - works a treat

    Where is there a concise list of these kinds of variables? I use Excel and its in-built functions extensively, however Macros have always been a cut and paste job - with only some idea as to what is going on behind the scenes.

    If I wanted to change the rest of the parameters for the graph (xMin/ymin etc), where should I start looking for what they're called?

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Slider/cell value to change x-axis range

    One way is to press F2 in the VBE, and search the Excel Library on a keyword - say MaximumScale, and then choose the class you are interested in - in this case Axis is the only class available, but often there are multiple classes to choose from. All the other properties and methods will be listed in the window.

    But, the best way is to record a macro performing the action you want:

    Please Login or Register  to view this content.
    And then cut down to what you really need:

    Please Login or Register  to view this content.
    And then modify it to fit your needs

    Please Login or Register  to view this content.
    Last edited by Bernie Deitrick; 11-13-2014 at 09:47 AM.

  5. #5
    Registered User
    Join Date
    09-10-2010
    Location
    UK, Marlborough
    MS-Off Ver
    365 (latest beta)
    Posts
    57

    Re: Slider/cell value to change x-axis range

    When I've a little more time I will dig deeper in to this, I find I can do most clever things that I need to using built in functions (I guess some of them are macros anyway) but need to learn a bit more about this to really get more out of it.

    Thanks again!

+ 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. How can I change Y axis range
    By g54n in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 11-25-2013, 01:22 PM
  2. How to change X axis range using dynamic ranges
    By james444 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-12-2013, 04:23 PM
  3. Using slider controls to select a required range of cells
    By Jordini in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-05-2013, 01:16 PM
  4. Using slider controls to select a required range of cells
    By Jordini in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-05-2013, 12:11 PM
  5. Replies: 1
    Last Post: 07-09-2011, 05:25 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