Right-Click to Paste Special Values

[DCSwearingen]

I have a person who needs to be able to copy and paste values very frequently. She would like me to set up a personal macro on her PC that will allow her to be able to right-click in a cell and have a context item that is paste values so she does not need to go to the paste special dialog continually.

E.g: She selects a range from one workbook and copies it, She then selects a cell in a second workbook and pastes the values into the second workbook. She does not mind pasting the formatting, but she does not want to paste the formulas as there are many named ranges in the formulas and if she inadvertently pastes everything, all of a sudden the next time she opens her second workbook there are links to the first. All she wants in the second workbook are the values, not the formulas.

[dominicb]

Good afternoon DCSwearingen

Something like the below will allow you to add a contect sensitive right click menu into the "cells" event, and add a paste special option. Out of interest, what version of Excel do you use - I use XL2003 and that already has such an option present.

Sub NewItem()
Dim PS As CommandBarControl
Set PS = CommandBars("Cell").Controls.Add
With PS
.Caption = "Paste Special"
.OnAction = "PS"
.BeginGroup = True
End With
End Sub

Sub PS()
Selection.PasteSpecial Paste:=xlPasteValues
End Sub

Sub RemoveItem()
On Error Resume Next
CommandBars("Cell").Controls("Paste Special").Delete
End Sub

HTH

DominicB

[DCSwearingen]

To answer your question, I am using Excel 2003, but the person I am trying to help uses Excel 2000.

My right-click context menu has Paste Special, but when selected I need to select Values from the dialog and then select OK.

She says she does this between 50 and 100 times for the report she is preparing within the first hour of the day. Reducing the number of mouse clicks from three to one in the paste operation will save her between 100 and 200 mouse clicks in that hour.

She is really good on a keyboard, if there is a keyboard shortcut to paste values, that option may be even better for her.

[dominicb]

Hi DCSwearingen

There is no keyboard shortcut for the Paste Special function, apart from pressing Alt + E, S to open the dialog box. You can use the code I gave you (run NewItem) to add a new menu item to the right click menu, or you could just use the macro PS and set that up with a shortcut key.

HTH

DominicB

[DCSwearingen]

Again,
Thank you for your time!!

How do I position the new context menu item so it is just below the standard Paste Special context menu item?

[DCSwearingen]

I think I am done after this question is answered.

I figured out how to position the Paste Values in the context menu by using the before variant.

But how do I dim the control if there is nothing in the clipboard to paste?

[dominicb]

Hi DCSwearingen

But how do I dim the control if there is nothing in the clipboard to paste?

You're going to have to do some extra programming around this issue to determine whether the clipboard is empty or not, but if you think it is worth the extra effort, the control you want to effect in the CommandBarControl is the "Enabled" property, thus :

.Enabled = False

Where False will show the control as ghosted out, True will show it as usable.

HTH

DominicB