Solved

Overriding Excel's default handling of cut, copy and paste

Posted on 2014-02-23
10
324 Views
Last Modified: 2014-02-28
I am looking for a way to make my Excel workbook default to always pasting as "values" to prevent users from messing up the formatting. I came across this code below in the Professional Excel Development book, but it's not working as hoped. I don't know if the problem is with the code or with where I have pasted it into the workbook. I tried pasting it into the worksheet, and when that didn't have an impact, tried it in the workbook.

The code I used is as follows:


Dim mbCut As Boolean
Dim mrngSource As Range

'Initialise cell copy-paste
Public Sub InitCutCopyPaste()

    'Hook all the cut, copy and paste keystrokes
    Application.OnKey "^X", "DoCut"
    Application.OnKey "^x", "DoCut"
    Application.OnKey "+{DEL}", "DoCut"

    Application.OnKey "^C", "DoCopy"
    Application.OnKey "^c", "DoCopy"
    Application.OnKey "^{INSERT}", "DoCopy"

    Application.OnKey "^V", "DoPaste"
    Application.OnKey "^v", "DoPaste"
    Application.OnKey "+{INSERT}", "DoPaste"

    Application.OnKey "{ENTER}", "DoPaste"
    Application.OnKey "~", "DoPaste"

    'Switch off drag/drop
    Application.CellDragAndDrop = False

End Sub

'Handle Cutting cells
Public Sub DoCut()

    If TypeOf Selection Is Range Then
     mbCut = True
     Set mrngSource = Selection
     Selection.Copy
    Else
     Set mrngSource = Nothing
     Selection.Cut
    End If

End Sub

'Handle Copying cells
Public Sub DoCopy()

    If TypeOf Selection Is Range Then
        mbCut = False
        Set mrngSource = Selection
    Else
        Set mrngSource = Nothing
    End If
    
    Selection.Copy
End Sub

'Handle Pasting cells
Public Sub DoPaste()
    If Application.CutCopyMode And Not mrngSource Is Nothing Then
        Selection.PasteSpecial xlValues
        If mbCut Then
            mrngSource.ClearContents
        End If
        
        Application.CutCopyMode = False
    Else
        ActiveSheet.Paste
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Private Sub Workbook_Open()

End Sub
[b][/b]

Open in new window

0
Comment
Question by:Andreamary
  • 5
  • 3
  • 2
10 Comments
 
LVL 26

Accepted Solution

by:
MacroShadow earned 500 total points
ID: 39881634
Put the code in a regular module, and run the InitCutCopyPaste sub, then it will behave as you expect.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39881635
Hi,

You were almost there!

Within the attached example workbook, I have placed the following code within the "ThisWorkbook" ("wbkQ_28372327") code module:

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)

  Application.OnKey "^X"
  Application.OnKey "^x"
  Application.OnKey "+{DEL}"

  Application.OnKey "^C"
  Application.OnKey "^c"
  Application.OnKey "^{INSERT}"

  Application.OnKey "^V"
  Application.OnKey "^v"
  Application.OnKey "+{INSERT}"

  Application.OnKey "{ENTER}"
  Application.OnKey "~"

End Sub
Private Sub Workbook_Open()

  Call InitCutCopyPaste
  
End Sub

Open in new window


Note that the Workbook_BeforeClose(...) event code is not within your original code listing.  The purpose of these code statements is to return the functionality of all the keyboard shortcuts (the "hooks") prior to the workbook being closed (so other workbooks opened thereafter are not affected).  However, workbooks open concurrently within the same EXCEL.exe session, will also only allow pasting "as values".

Within a new (Public) code module, "basQ_28372327", I have placed the following code:

Option Explicit

Dim mbCut                                               As Boolean
Dim mrngSource                                          As Range
Public Sub InitCutCopyPaste()

'Initialise cell copy-paste

'Hook all the cut, copy and paste keystrokes
  Application.OnKey "^X", "DoCut"
  Application.OnKey "^x", "DoCut"
  Application.OnKey "+{DEL}", "DoCut"

  Application.OnKey "^C", "DoCopy"
  Application.OnKey "^c", "DoCopy"
  Application.OnKey "^{INSERT}", "DoCopy"

  Application.OnKey "^V", "DoPaste"
  Application.OnKey "^v", "DoPaste"
  Application.OnKey "+{INSERT}", "DoPaste"

  Application.OnKey "{ENTER}", "DoPaste"
  Application.OnKey "~", "DoPaste"

'Switch off drag/drop
  Application.CellDragAndDrop = False

End Sub
Public Sub DoCut()

'Handle Cutting cells

  If TypeOf Selection Is Range Then
     mbCut = True
     Set mrngSource = Selection
     Selection.Copy
  Else
     Set mrngSource = Nothing
     Selection.Cut
  End If ' If TypeOf Selection Is Range Then

End Sub
Public Sub DoCopy()

'Handle Copying cells

  If TypeOf Selection Is Range Then
      mbCut = False
      Set mrngSource = Selection
  Else
      Set mrngSource = Nothing
  End If ' If TypeOf Selection Is Range Then
    
  Selection.Copy

End Sub
Public Sub DoPaste()
 
'Handle Pasting cells
    
  If Application.CutCopyMode And _
     Not mrngSource Is Nothing Then
     Selection.PasteSpecial xlValues
     If mbCut Then
        mrngSource.ClearContents
     End If ' If mbCut Then
        
     Application.CutCopyMode = False
  Else
     ActiveSheet.Paste
  End If ' If Application.CutCopyMode And Not mrngSource Is Nothing Then

End Sub

Open in new window


BFN,

fp.
Q-28372327.xls
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39881643
Note it will only work for the keyboard shortcuts not for Excel's right-click context menu.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39881796
To address MacroShadow's concerns, I would suggest looking at the previous question thread, "How to Disable Cut, Copy and Paste in Excel ?":

[ http://www.experts-exchange.com/Q_27965314.html ]

Also for information, "Paste special causes loss of cell data validation":

[ http://www.experts-exchange.com/Q_23982408.html ]

Other threads exists on a similar topic, too.
0
 

Author Closing Comment

by:Andreamary
ID: 39893540
This did the trick! Thanks to everyone who responded.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39893987
Two of us responded.  You credited the other "Expert" even though given my extended response (including an example workbook, & advice about adding a "Workbook_BeforeClose()" event), I was only 3 seconds later than the first reply (of a single sentence).

(Sigh)
0
 

Author Comment

by:Andreamary
ID: 39894469
Hi fanpages,

Thanks for flagging this. In view of your note, I am now concerned that I may not have handled this fairly or correctly. I was impressed with the depth of your response, but thought that awarding the points to the "first out of the gate" with the solution that solved my problem was the protocol on this site. And I have to confess that because my problem was solved with the first response, I didn't proceed further. :-(

Please advise as to what I should do in this situation. The expertise on this website is invaluable to me, and I want to make sure I'm acknowledging and awarding points correctly.

Andrea
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39894904
Please don't worry, Andrea.

The fact you came back to explain what you did, is worth more to me than any point allocation.

For your information, "first out of the gate" is not the protocol.

You may award any contributing "Expert" the resolution to a question, & also highlight other "Experts" within the same question thread that contributed to the resolution.

If you ever feel you have made a mistake allocation points &/or the "accepted" answer, you may simply click the "Request Attention" icon, complete the form with any details you feel relevant, & submit it.  One of the site staff will then look at the thread, & act accordingly.

In instances such as this, though, it is usually common practice to split the resolution (& hence, the point allocation) between the "Experts" that answered in close proximity.

Common practice, though, not a "hard & fast" rule.

As I said, please don't worry about it.

Acknowledgement of my contribution is praise enough :)

Good luck with the rest of your project.

BFN,

fp.
0
 

Author Comment

by:Andreamary
ID: 39895292
Thanks very much, fp, for your gracious and helpful response...

Andrea
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39895491
You're very welcome.

Thank you for not mentioning my typographical error, corrected with the italicised word in the prefix to this sentence; "If you ever feel you have made a mistake allocating points...".

:)
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

With the internet and the ease of information transference, many professional jobs can be done anywhere today.  Why should it make a difference whether an x-ray is read in India or the United States as long as the radiologist is qualified?   Outso…
A high-level exploration of how our ever-increasing access to information has changed the way we do our jobs.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now