Solved

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

Posted on 2014-02-23
10
384 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
10 Comments
 
LVL 27

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 27

Expert Comment

by:MacroShadow
ID: 39881643
Note it will only work for the keyboard shortcuts not for Excel's right-click context menu.
0
Plug and play, no additional software required!

The ATEN UE3310 USB3.1 Gen1 Extender Cable allows users to extend the distance between the computer and USB devices up to 10 m (33 ft). The UE3310 is a high-quality, cost-effective solution for professional environments such as hospitals, factories and business facilities.

 
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
 
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
When asking a question in a forum or creating documentation, screenshots are vital tools that can convey a lot more information and save you and your reader a lot of time
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

624 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