Page 1 of 4 123 ... LastLast
Results 1 to 15 of 51
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Disabling paste into cells

    I have a worksheet created in Excel 2010 that includes a couple of columns with data validation, but if users paste data into those columns, the data validation doesn't work (and the formatting also gets overwritten).

    Is there a way to disable the paste function in specified cells, so that users cannot paste data into them but must type data into them manually?
    Last edited by Murgatroyd; 2015-06-29 at 20:51.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Murgatroyd,

    The following code will disable the right click in column A. You can add as many columns as you want.

    Place in the worksheet module:
    Code:
    Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column = 1 Then
        Cancel = True
    End If
    End Sub
    This will not stop them from selecting a cell in column A then pressing Ctrl-V.

    HTH,
    Maud

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your reply. However, I don't want to disable right-click altogether, as that includes other options that I want to remain enabled (e.g., Insert Comment). I only want to disable pasting (whether by right-click or Ctrl+V), etc in a specific range of cells.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Murgatroyd,

    In addition to Maud's fine code if you add the following it will trap the Ctrl+v as well as selecting cells and pressing either Enter key.

    In the WorkSheet Module:
    Code:
    Option Explicit
    
    Private Sub Workbook_Open()
    
        Application.OnKey "^v", "KillPaste"
        Application.OnKey "{Enter}", "CheckCopyMode"
        Application.OnKey "~", "CheckCopyMode"
        
    End Sub
    In a Standard Module:
    Code:
    Option Explicit
    
    Sub KillPaste()
    
      Application.CutCopyMode = False
    
    '*** MsgBox for Testing only remove for production ***
    
      MsgBox "No Paste for YOU!", _
             vbOKOnly + vbCritical, _
             "The Paste Natzie says..."
    End Sub
    
    Sub CheckCopyMode()
    
       Dim rngPreventPaste As Range
       
       Set rngPreventPaste = Range("C:F")   '*** No Paste Columns ***
    
       If Application.CutCopyMode Then
         If Intersect(ActiveCell, rngPreventPaste) Is Nothing Then
           ActiveSheet.Paste
       Else
           KillPaste
         End If
       End If
       
    End Sub
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Murgatroyd,

    This revision of Maud's code will only disable the context menu (right-click) if a copy is in progress.
    Code:
    Option Explicit
    
    Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    
       Dim rngPreventPaste As Range
       
       Set rngPreventPaste = Range("C:F")   '*** No Paste Columns ***
    
       If Application.CutCopyMode Then
         If Intersect(ActiveCell, rngPreventPaste) Is Nothing Then
           Cancel = False
         Else
           KillPaste
           Cancel = True
         End If
       Else
         Cancel = False
       End If
    End Sub
    I think with these items combined it is about as close to what you want as you are going to get IMHO.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Maudibe (2015-06-29)

  7. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Very Nice RG. Going in my bag of tricks

  8. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your reply, RetiredGeek. However, I'm a bit confused ... can you clarify which of the four blocks of code mentioned above I need to use, and where they go ("Worksheet Module", "Standard Module"?) ... thanks.

    Also, will this work for whole columns only, or can it work for just a range of cells (e.g., D2261 and E22:E61)?
    Last edited by Murgatroyd; 2015-06-29 at 23:15.

  9. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Murgatroyd,

    You use all four blocks of code in posts 4 & 5 to trap all the possibilities! Post 4 specifies where those blocks go. Post 5 goes in the Worksheet module as specified by Maud in his original post #2.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #9
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for the clarification. I tried this, and it works with right-click, but not with Ctrl+V or Enter. I have attached my test file FYI ... am I doing something wrong?
    Attached Files Attached Files

  11. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Disabling Paste into a range of cells..

    Hi

    ..here's my code for disabling a Paste (or PasteSpecial) into a range:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    '**********************************************
    'CHECK FOR CHANGE DETECTED IN SPECIFIED RANGE..
    '**********************************************
    If Not Intersect(Target, Range("B6:C14")) Is Nothing Then   '<<set range required
    zUndo = Application.CommandBars("Standard").Controls("&Undo").List(1)
    If Left(zUndo, 5) = "Paste" Then    'i.e. Paste or PasteSpecial
    With Application                    'use shortcut
    .ScreenUpdating = False             'freeze display till ready
    .EnableEvents = False               'turn events OFF while making changes to sheet
    .Undo                               'Undo last operation
    .EnableEvents = True                'turn event trapping back ON
    .CutCopyMode = False                'cancel highlighted range
    saywhat = "Paste operation not allowed in this area!" & vbCr    'message box text
    saywhat = saywhat & "This paste will be cancelled"              'add text
    btns = vbOKOnly + vbExclamation             'message box buttons
    zSheet = ActiveSheet.Name                   'sheet name
    boxtitle = zSheet & " Sheet"                'message box heading
    answer = MsgBox(saywhat, btns, boxtitle)    'display message box
    Exit Sub                                    'nothing else to do
    End With                            'end of shortcut
    End If                              'end of check for a Paste operation
    End If                              'end of test for change in specified range
    '**********************************************
    
    End Sub
    ..see attached workbook for example.

    NOTE: You can still overwrite within any range using cell drag and drop, or autofill.
    Do you want a fix for those as well?????

    zeddy
    Attached Files Attached Files

  12. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi RG

    ..if you copy a block of cells (say 4cols x 5rows) and then paste them into a cell in, say, column B, then your 'paste prevention' routine will NOT stop the pasting of data into the specified disallowed columns!

    ..just to let you know.

    zeddy

  13. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Murgatroyd

    ..if you want your Users to fix their own mistakes, you could include this in the sheet code:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.CircleInvalid
    End Sub
    ..any cells with Data Validation that have had data 'pasted' over them will then show a nice red circle if the value isn't allowed for the cell.

    zeddy

  14. #13
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your replies, Zeddy. That works for me. As you say, it doesn't prevent drag/drop or autofill, but in my case, users are (hopefully) unlikely to do that. However, is there a way to specify cells that are not in a contiguous range (e.g., A1:C3 AND E1:G3)?

    Where should the "circle invalid" line go in the code?
    Last edited by Murgatroyd; 2015-06-30 at 07:52.

  15. #14
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by Murgatroyd View Post
    Thanks for the clarification. I tried this, and it works with right-click, but not with Ctrl+V or Enter. I have attached my test file FYI ... am I doing something wrong?
    You had the code in the wrong modules.

    Here's your file with the code in the right modules: Test disable pasting into B3-D5.xlsm

    To get multiple ranges use:
    Set rngPreventPaste = Union(Range("B3:D5"), Range("G1:I5")) '*** No Paste Columns ***
    -- or --
    Set rngPreventPaste = Range("B3:D5, G1:I5")


    HTH
    Last edited by RetiredGeek; 2015-06-30 at 08:15.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  16. #15
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Murgatroyd

    ..as per my post#10 , with a little tweak to specify multiple ranges:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    '**********************************************
    'CHECK FOR CHANGE DETECTED IN SPECIFIED RANGE..
    '**********************************************
    If Not Intersect(Target, [A1:C3, E1:G3]) Is Nothing Then  '<<set range required
    zUndo = Application.CommandBars("Standard").Controls("&Undo").List(1)
    If Left(zUndo, 5) = "Paste" Then    'i.e. Paste or PasteSpecial
    With Application                    'use shortcut
    .ScreenUpdating = False             'freeze display till ready
    .EnableEvents = False               'turn events OFF while making changes to sheet
    .Undo                               'Undo last operation
    .EnableEvents = True                'turn event trapping back ON
    .CutCopyMode = False                'cancel highlighted range
    saywhat = "Paste operation not allowed in this area!" & vbCr    'message box text
    saywhat = saywhat & "This paste will be cancelled"              'add text
    btns = vbOKOnly + vbExclamation             'message box buttons
    zSheet = ActiveSheet.Name                   'sheet name
    boxtitle = zSheet & " Sheet"                'message box heading
    answer = MsgBox(saywhat, btns, boxtitle)    'display message box
    Exit Sub                                    'nothing else to do
    End With                            'end of shortcut
    End If                              'end of check for a Paste operation
    End If                              'end of test for change in specified range
    '**********************************************
    
    End Sub
    If you want to include single cells as well, use something like this:
    [A1:C3, E1:G3, M25, M4, A10] in the Intersect(Target, xxx) as above.

    zeddy
    Attached Files Attached Files
    Last edited by zeddy; 2015-06-30 at 09:17.

Page 1 of 4 123 ... LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •