+ Reply to Thread
Results 1 to 5 of 5

Add Shape (arrow) to an Excel Chart AND auto re-position

  1. #1
    Registered User
    Join Date
    04-28-2012
    Location
    Charlotte, NC
    MS-Off Ver
    Excel 2003, 07 & 10, 16
    Posts
    26

    Add Shape (arrow) to an Excel Chart AND auto re-position

    Hi -

    I have a Combo Chart (primary series is a scatter graph; secondary series is a line graph). The Secondary Axis is a straight line (single $ Amount). I've manually added a Shape called "Left Arrow", and have added some text (linked to a cell) that pertains to the Secondary Axis. The shape is on the right side of the graph where the secondary vertical axis would be.

    Question is: Using VBA, how can I auto adjust the position of the arrow shape to align with the Secondary Axis that will change each time I update the graph? I have tried to use the .Top alignment for the arrow shape via VBA, but I can't seem to match this ".Top" with anything on the Secondary Axis.

    As mentioned, the Secondary Axis can & probably will change with each refresh, so, if using VBA I can determine the Secondary Line amount, can I use that to help the arrow shape to find the right position?

    Anybody have an example / code?

    Any help is greatly appreciated!

    thanks,
    GRC
    Last edited by grcaz; 10-25-2017 at 08:48 AM.

  2. #2
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Add Shape (arrow) to an Excel Chart AND auto re-position

    Hi,

    Can you post a sample workbook? It's often easier to plot a dummy series/point with a shape marker since that will automatically adjust with the axis scale.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  3. #3
    Registered User
    Join Date
    04-28-2012
    Location
    Charlotte, NC
    MS-Off Ver
    Excel 2003, 07 & 10, 16
    Posts
    26

    Re: Add Shape (arrow) to an Excel Chart AND auto re-position

    Hi xlnitwit -

    Attached is the file. I've added a simple macro that changes the source data so you can see that the arrow does not auto align after the macro has run.

    Any help is greatly appreciated.

    Thanks!

    GRC
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Add Shape (arrow) to an Excel Chart AND auto re-position

    As xlnitwit says, it is usually easier to attach the picture to an actual data point in the chart rather than leave it as a separate shape then try to position it. This is what I did (in Excel 2007):

    1) Select the arrow shape. Ctrl-C to copy to clipboard.
    2) Select the target line data series, then click on the rightmost point to select that data point only.
    3) ctrl-V to paste the arrow as the data point marker.

    The arrow is centered over the data point rather than being off to the right. This could be fixed by editing the shape so that the point of the arrow is at the center of the shape rather than at the edge.

    With the shape attached to the data point like this, it will move as the data point moves.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Registered User
    Join Date
    04-28-2012
    Location
    Charlotte, NC
    MS-Off Ver
    Excel 2003, 07 & 10, 16
    Posts
    26

    Re: Add Shape (arrow) to an Excel Chart AND auto re-position

    MrShorty - thanks for the clarificiation! I did not know that was possible. I was able to incorporate that into my file and was able to get it to work. I did have to delete the direct link to text in a cell and instead customized the Data Label "Values".

    Learn something new everyday....

    thanks again!

    GRC

+ 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. Replies: 4
    Last Post: 01-26-2016, 05:08 AM
  2. [SOLVED] How to push painted cell using the arrow keys or shape-arrow in Excel - VBA-Offset?
    By Dumy in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-04-2015, 05:05 AM
  3. Excel 2007 : Shape Position
    By bwkiefer in forum Excel General
    Replies: 3
    Last Post: 01-26-2014, 10:07 AM
  4. ADD arrow shape button to view next record
    By dingdang in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-20-2013, 11:01 AM
  5. Conditional Shape of an arrow based on cell value
    By ron2k_1 in forum Excel General
    Replies: 1
    Last Post: 06-30-2010, 08:51 PM
  6. position of shape
    By jmoffat in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-12-2006, 04:02 AM
  7. auto position of data labels in a line chart
    By Nick Turner in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 10-18-2005, 07:05 PM

Tags for this Thread

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