Excel2000...use Ctrl-V, not Paste, in macro to Format Axis in a ch

[MacroPete]

In a macro I would like to use Ctrl-V to paste minimum and maximum values
into a chart's "Format Axis".

But "Record Macro" translates the keypress Ctrl-V into "Paste".

In manual (non-macro) mode, "Edit/Paste" does not work, but Ctrl-V does. So,
in the macro, since Ctrl-V is translated into Paste, the macro does not work.

How do I get the macro routine to recognize the keypress Ctrl-V without the
macro translating it to Paste.

I have checked out ASCII and ANSI codes with no success.

 

[John Mansfield]

Assuming you are accessing your data from the same workbook in which your
chart exists, would it be easier to tie that data directly to your chart via
cell references using a macro rather than copying and pasting? If so John
has an example of the macro to use on his site . . .

http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html

 

[Nick Hodge]

MacroPete

You can replace the paste line with a sendkeys method/statement like so

Application.SendKeys ("^v") 'Excel's method
SendKeys("^v") 'VBA's statement

Caution: You may find if using this on Windows Vista with Excel versions
before Excel2007 that the Excel method will not work with Vista's UAC (User
Access Control) switched on (default). The VBA statement will work. Both
will work in Excel 2007 (go figure)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[MacroPete]

Thank you John ... much appreciated.

For now I am attempting Nick's SendKey approach since it fits in with my
current code.

Thx again.

 

[MacroPete]

Nick,

I am having trouble with SendKey. I run Excel2000 in XP SP2.

Rather than mess with using SendKey to paste (^v) in my chart, I came up
with a little macro to simply enter a value (151) into cell A1, but receive a
compile error.

Here's my code ...

Sub SKeys()
'
' SKeys Macro
' Macro recorded 3/23/2007 by MacroPete
'
' Keyboard Shortcut: Ctrl+Shift+S
'
Range("A1").Select
ActiveCell.FormulaR1C1 = Application.SendKeys("151")
Range("A2").Select
End Sub
************************

I also tried ... ActiveCell.FormulaR1C1 = SendKeys("151")

and received the same compile error.

Might you correct my above code?

My thinking is that once I get SendKey to work re the above, I'll get it
working in my chart.

Thank you very much.

 

[Jon Peltier]

Why not use the object model parts provided for this:

With ActiveChart.Axes(xlValue, xlPrimary)
.MinimumScale = 0
.MaximumScale = 100
.MajorUnit = 10
End With

- Jon

 

[MacroPete]

John,

I adapted some of your suggested code and it works really well. Thank you
very much.

Nick ... if you're reading, and would still care to pass along help with
SendKeys, that'd be great.