Can't copy and paste after using VBA code. Any help please

[Excel addicted]

Hello guys
I'm so glad to be part of this forum and to learn and share excel best practices.
I'm a novice in VBA and need some help on the following. I created a check-list with a multirow highlight feature through VBA that I got inspired from an amazing guy.
However I can't copy and paste anymore. Plus the default undo button in excel is deactivated.
Here is following code :






Private Sub Worksheet_SelectionChange(ByVal Target As Range)


If Not Intersect(Target, Range(" D22:I46 ")) Is Nothing Then
Dim Cell As Range
Range("A4").NumberFormat = "@"
If Target.Count > 1 And Target.Count < 50 Then
StopCode
For Each Cell In Selection
Range("A4").Value = Range("A4").Value & "(" & Cell.Row - 21 & ")"
Next Cell
ResetCode
Else
Range("A4").Value = "(" & Target.Row - 21 & ")"
End If
Else
Range("A4").ClearContents
End If
End Sub





Of course I added another code which is linked to the one above to make the application run faster




Sub StopCode()
With Application
.EnableEvents = False
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
End Sub


Sub ResetCode()
With Application
.EnableEvents = True
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub






Thanks in advance for your time and I appreciate your effort in case you find the solution and try to explain it in a easy way

 

[pbornemeier]

Change

    Range("A4").ClearContents

to

    Range("A4").Value = vbNullString

Excel copy (from a cell) & paste (to a cell) relies on the first cell still having the "marching ants" effect on the selected cell border that occurs when the cell is copied.
As soon as you selected the cell you wanted to paste to, your Worksheet_SelectionChange code executed and the .ClearContents line caused the copy cell to lose the 'I am being copied' status.
Setting the value of A4 to vbnullstring did not cause this loss - I am not sure why

Please use code tags to post your code. It retains indents.

 

[Excel addicted]

Thanks for your reply pbornemeier. I changed the line of code following your advice but it seems the issue is still present. I can't copy or paste.

 

[pbornemeier]

I tested the code with my modification by copying a cell inside the D22:I46 range and pasting it outside of that range.
I also tested copying a cell outside of that range to another cell outside that range.
In both of those cases the copy & paste worked.

So copying any range and pasting it outside of the D22:I46 range works.
However attempting to paste any values to a range that overlapped D22:I46 resulted in the paste operation failing.

I could not figure out a way to avoid this with the current code.

Please describe what you want to accomplish with the code and perhaps we can work out some alternate code.

 

[Excel addicted]

I added a link to my workbook so you can check the issue. https://drive.google.com/open?id=1Wv-pHxhPK8kQOnJ8rvx64SW_SQ6PRujF

Also I added a second issue I faced in case you can help. I'm thankful so much for your support

 

[pbornemeier]

I could not figure out a way to let you copy and paste as long as the code was triggered by selection change and you wanted to clear A4 whenever. See if this code with 2 ActiveX command buttons will work for your requirements.

Option Explicit
'Add 2 Active X command buttons to the worksheet and add the following code to that worksheet's code page

Private Sub cmdCopySelectedRowsToA4_Click()
    'Copy the row number of cells selected to A4
    'Rows will only be include once
    'If no cells are selected, clear A4

    'For any cell selected in D22:I46, add the current date to column G in that row
    
    Dim rngCell As Range
    Dim rngArea As Range
    Dim sOutput As String
    Dim sRow As String
    
    If Not Intersect(Selection.Cells, Range("D22:I46")) Is Nothing Then
        'Something inside of "D22:I46" is selected
        For Each rngArea In Selection.Areas
            For Each rngCell In Intersect(rngArea.EntireRow, Range("G22:G46"), Range("D22:I46")).Cells
                sRow = rngCell.Row - 21
                If InStr(sOutput, "," & sRow) = 0 Then
                    'Row has not yet been added to sOutput
                    sOutput = sOutput & ", " & sRow
                End If
            Next
        Next
        If sOutput <> vbNullString Then
            sOutput = Mid(sOutput, 2)
            Range("A4").Value = "(" & sOutput & ")"
        End If
    Else
        Range("A4").Value = vbNullString
    End If
End Sub

Private Sub cmdAddDateToColumnG_Click()
    Dim rngCell As Range
    Dim rngArea As Range
    
    If Not Intersect(Selection.Cells, Range("D22:I46")) Is Nothing Then
        'Something inside of "D22:I46" is selected
        If Selection.Cells.Count = Intersect(Selection.Cells, Range("D22:I46")).Cells.Count Then
            'Only cells inside D22:I46 are selected
            For Each rngArea In Selection.Areas
                For Each rngCell In Intersect(rngArea.EntireRow, Range("G22:G46")).Cells
                    rngCell.Value = Int(Now())
                Next
            Next
        End If
    End If
End Sub

 

[Excel addicted]

The ActiveX command button works great . You saved me a a lot of time ! Thanks so much !!!
For the copy and paste issue , I will try to find an alternative way to avoid it.
I will let you know in case I find a solution for that
Kind regards

 

[pbornemeier]

If you use the 2 buttons then you can remove the Worksheet_SelectionChange code and the copy/paste problem should go away.

 

[Excel addicted]

I can't do that because I want the rows selected within the range to be formated in a different color ( blue for my case). Unless there is another way to keep the conditional formatting active.