Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get back from chart to spreadsheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get back from chart to spreadsheet
If the chart is embedded on a worksheet (which it appears it is)
ActiveCell.Activate -- Regards, Tom Ogilvy "nsv" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get back from chart to spreadsheet
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get back from chart to spreadsheet
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get back from chart to spreadsheet
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get back from chart to spreadsheet
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Refering back to a spreadsheet saved on the internet | Excel Worksheet Functions | |||
Click inside both Spreadsheet and VBE - Back and forth | Excel Discussion (Misc queries) | |||
can't use scrollbars on large spreadsheet, they bounce back | Excel Worksheet Functions | |||
How do you get a spreadsheet button back? | Excel Discussion (Misc queries) | |||
Data changed in the back-up spreadsheet | Excel Discussion (Misc queries) |