bug in Application.CutCopyMode

AaronReese

New Member
Joined
Jan 29, 2019
Messages
1
Guys,

This one has me stumped

I have a spreadsheet where the users would like to copy a cell and then highlight a range of cells (in the same column) and paste down the value. Unfortunately, some of the rows are locked and the locked rows cannot (and should not) be overwritten

I have the following procedure called from a custom context menu line
Code:
Sub customPaste()


    'MsgBox "custom Paste"
    'MsgBox "message2"
    Dim myCell As Range
    Dim previousCell As Range
    Dim workingRange As Range
    
    Set workingRange = Selection
    
    Call UnprotectSheets
    
    'If Application.CutCopyMode = 0 Then
    '    MsgBox "WTF!"
       ' GoTo errhandler
    'End If
    
    For Each myCell In workingRange
        If (Not (myCell.Locked)) Then
            myCell.PasteSpecial Paste:=xlPasteFormulas
            DoEvents
            If myCell.Address <> workingRange(workingRange.Count).Address Then
                myCell.Copy
            End If
        End If
    Next myCell
   
'errhandler:


    Call ProtectSheets
End Sub
Once it has been run, the CutCopyMode appears to be stuck at zero and the macro will then throw a runtime error. After the runtime error it seems to be OK again. If you comment out the error handler, once it has run sucessfully it will continue to jump to the errhandler until you force a runtime error.

The protection around mycell.copy was an attempt to stop it from selecting the last cell and leaving the application in a Copy state (marching ants)
 
Last edited by a moderator:

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
This is what is happening:
- Copy cell
- Unprotect sheet
- The memory is cleaned, you no longer have data in the clipboard.
- When you execute the paste, it sends error.

----
You can do the following,

Step 1, select the cell to copy,
Step 2, then select the paste range.

Code:
Sub Copy_Cell()
    Dim cell As Range, workingRange As Range, myCell As Range
    
    On Error Resume Next
    With Application
        Set cell = .InputBox("Select the cell you would like to copy", "COPY CELL", _
            Default:=Selection.Address, Type:=8)
        If cell Is Nothing Then Exit Sub
        Set workingRange = .InputBox("Highlight a range of cells to paste", "COPY CELL", _
            Type:=8)
        If workingRange Is Nothing Then Exit Sub
    End With
    On Error GoTo 0
    ActiveSheet.Unprotect
    cell.Copy
    For Each myCell In workingRange
        If Not myCell.Locked Then
            myCell.PasteSpecial Paste:=xlPasteFormulas
        End If
    Next myCell
    ActiveSheet.Protect
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top