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

 

[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]

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

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]

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

NSV

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