Get back from chart to spreadsheet

[nsv]

I have a tiny piece of code which I use to adjust the axis on a chart after having changed parameters on the spreadsheet. After the adjustment I want the cursor to return to the cell I used last, and the only way I can think of is to write like this:

...
.ScaleType = xlLogarithmic
.DisplayUnit = xlNone
End With
SendKeys "{esc}"
SendKeys "{esc}"
SendKeys "{esc}"
End Sub

- and all three ESC's are necessary. The first deselects axis for chart, the next deselects chart for pushbutton and the third deselects pushbotton for spreadsheet.
I don't like this solution. I find it clumsy and would like a more elegant approach.

NSV

[Tom Ogilvy]

If the chart is embedded on a worksheet (which it appears it is)

ActiveCell.Activate

--
Regards,
Tom Ogilvy

"nsv" <[email protected]> wrote in message
news:[email protected]...
>
> I have a tiny piece of code which I use to adjust the axis on a chart
> after having changed parameters on the spreadsheet. After the
> adjustment I want the cursor to return to the cell I used last, and the
> only way I can think of is to write like this:
>
> ..
> ScaleType = xlLogarithmic
> DisplayUnit = xlNone
> End With
> SendKeys "{esc}"
> SendKeys "{esc}"
> SendKeys "{esc}"
> End Sub
>
> - and all three ESC's are necessary. The first deselects axis for
> chart, the next deselects chart for pushbutton and the third deselects
> pushbotton for spreadsheet.
> I don't like this solution. I find it clumsy and would like a more
> elegant approach.
>
> NSV
>
>
> --
> nsv
> ------------------------------------------------------------------------
> nsv's Profile:
http://www.excelforum.com/member.php...o&userid=26500
> View this thread: http://www.excelforum.com/showthread...hreadid=480063
>

[nsv]

Thanks Tom, but it does not work. I get a run time error 91 with the message 'Object variable or With block variable not set'.

The chart is embedded allright, so you're right in assuming that.

I do not want to return to a one specific cell after the axis adjustment. I want return to the cell where I was just before I activated the macro.

NSV

[John Coleman]

nsv wrote:
> Thanks Tom, but it does not work. I get a run time error 91 with the
> message 'Object variable or With block variable not set'.
>
> The chart is embedded allright, so you're right in assuming that.
>
> I do not want to return to a one specific cell after the axis
> adjustment. I want return to the cell where I was just before I
> activated the macro.
>
> NSV
>
>
> --
> nsv
> ------------------------------------------------------------------------
> nsv's Profile: http://www.excelforum.com/member.php...o&userid=26500
> View this thread: http://www.excelforum.com/showthread...hreadid=480063


Maybe something like

Sub ...
Dim placeholder As Range
Set placeholder = ActiveCell
..
..
..(code to adjust chart)
..
..
placeholder.Activate
end Sub

Hope that helps

-John Coleman

[nsv]

Yes, it works - not that it saves any lines, but somhow I like this way much better.
Thanx for your help

NSV

[John Coleman]

nsv wrote:
> Yes, it works - not that it saves any lines, but somhow I like this way
> much better.
> Thanx for your help
>
> NSV
>
>
> --
> nsv
> ------------------------------------------------------------------------
> nsv's Profile: http://www.excelforum.com/member.php...o&userid=26500
> View this thread: http://www.excelforum.com/showthread...hreadid=480063

I'm glad it works - but maybe you can also consider a more global
solution. By declaring and setting chart variables it is possible to
modify a chart without selecting it (in which case the selection never
changes in the first place). The macro recorder records what you do
from the user interface (which involves a lot of selecting). VBA itself
can bypass all that selecting:

Dim ch As Chart
Set ch = Worksheets(1).ChartObjects(1).Chart
ch.Axes(xlCategory).MinimumScale = -10

etc.

you can also declare variables to represent things like series, etc.
If you post the relevant portions of your code I can (if it is not too
difficult) suggest some ways to avoid manipulating selections.

Hope that helps

-John Coleman