Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    IncrementTop, etc. with ChartObject (Excel 2000, SR1)

    Hi Folks,

    In PowerPoint, you can use arrow keys to precisely move an object within a slide. If you try the same thing on a Chart (on a sheet) in Excel, instead the selection rotates amongst the various chart components.

    I tried making some simple macros a-la
    <pre>Selection.IncrementLeft 1
    </pre>

    and attaching them to toolbar buttons, but Excel is very picky about what the Selection is. When you select a chart and the sizing handles are black, it is the ChartArea that is selected, not the chart itself. I also tried making a macro that does the selecting for you, which results in the sizing handles being white, and what is normally the range name box listing the selection as "Chart 1" instead of "Chart Area".

    In either case, I get "Object does not support that property or method" with respect to the IncrementTop.

    If I record a macro whilst dragging the chart, the resulting code treats the chart as Shapes("Chart 1"), instead of ChartObjects("Chart 1"), and it uses the IncrementTop property.

    What am I missing here?!? Any suggestions welcome. Thanks, in advance.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IncrementTop, etc. with ChartObject (Excel 2000, SR1)

    It is pretty usual for people to start running around screaming and cursing when they first start to try and line up (elements of) charts in Excel <vbg>.

    That being said, a small tip: If you hold the alt-key while dragging (or resizing) any object over a sheet, it anchors to the cell grid.

    About the Selection: If it is the chart only you wish to move, try this code:

    Option Explicit

    Sub NudgeChartUp()
    Dim oObject As Object
    Set oObject = Selection
    If TypeName(oObject) <> "ChartArea" And TypeName(oObject) <> "ChartObject" Then
    Do
    Set oObject = oObject.Parent
    Loop While TypeName(oObject) <> "ChartObject" And TypeName(oObject) <> "ChartArea" And TypeName(oObject) <> "Application"
    End If
    If TypeName(oObject) = "Application" Then
    MsgBox "Not a chart"
    Exit Sub
    End If
    oObject.Top = oObject.Top - 1
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: IncrementTop, etc. with ChartObject (Excel 2000, SR1)

    I think Jan is right - but two small comments.
    1) My personal preference is to insert a separate chart sheet. I find editing so much easier on the separate sheet. Also, printing is more predictable.
    2) I always forget this - but try zooming the page. Small moves become much easier on a large magnification.

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IncrementTop, etc. with ChartObject (Excel 2000, SR1)

    Hi Catherine,

    <<My personal preference is to insert a separate chart sheet>>

    Mine exactly. Avoids searching for it on your sheets as well <g>.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IncrementTop, etc. with ChartObject (Excel 2000, SR1)

    Jan,

    I use the Alt trick myself all the time. I was asking the tough question on behalf of a colleague.

    Meanwhile, thanks for the code, it does exactly what my colleague needs!

  6. #6
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IncrementTop, etc. with ChartObject (Excel 2000, SR1)

    Thanks for the advice. I too, always use a separate chart sheet, unless someone I'm working for wants side-by-side stuff.

    Zoom helps too. One template I made would automatically zoom to fit the chart sheet when the chart sheet was activated, but for some reason, on an NT system, the various zoom methods become disabled. Other evidence: the zoom drop-down was grayed out. But only on the NT systems.

  7. #7
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IncrementTop, etc. with ChartObject (Excel 2000, SR1)

    Jan,

    One change I had to make is:
    <pre>TypeName(oObject) <> "Chart Area"
    </pre>

    versus
    <pre>TypeName(oObject) <> "ChartArea"
    </pre>


  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IncrementTop, etc. with ChartObject (Excel 2000, SR1)

    <<One change I had to make is:

    TypeName(oObject) <> "Chart Area"
    versus>>

    I copied my working code into the message. Probably reformatting of the message text wrecked it a bit (or you made the typo <g>). I should have indicated the formatting myself.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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