How to force paste as formulae?

[Kelvin Stott]

Hello VBA experts,

How can I disable all paste special options EXCEPT for paste as formulae, so that pasting won't mess up comments, formatting & validation, etc.?

Also, how can I convert standard paste operation into paste as formulae?

Please help!

Thank you,
Kelvin

 

[JamesW]

Hi there,

Take a look at this:

http://www.mrexcel.com/forum/showthread.php?t=230718

They used Values only, but you can change that to Formulas only.

 

[Kelvin Stott]

Many thanks, I'll try to play around with the code and report back...

 

[Kelvin Stott]

Re: How to force paste as formulas?

I tried the above, but it's not quite what I'm looking for. I just want to convert the standard "Paste" menu option into automatic "Paste as formulas". I tried different variations of the following, but none of them worked:

CommandBars(1).Controls("Edit").Controls("Paste").Enabled = x1PasteFormulas

What am I doing wrong?

 

[Kelvin Stott]

Re: How to force paste as formulas?

CommandBars(1).Controls("Edit").Controls("Paste").Enabled = x1PasteFormulas

Please, can anyone help me correct the syntax in this one line of code??