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

Excel addicted

New Member
Joined
Sep 17, 2019
Messages
7
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

Well-known Member
Joined
May 24, 2005
Messages
3,692
Change
Code:
    Range("A4").ClearContents
to
Code:
    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.
 

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,692
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.
 

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,692
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.

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

New Member
Joined
Sep 17, 2019
Messages
7
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

Well-known Member
Joined
May 24, 2005
Messages
3,692
If you use the 2 buttons then you can remove the Worksheet_SelectionChange code and the copy/paste problem should go away.
 

Excel addicted

New Member
Joined
Sep 17, 2019
Messages
7
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.
 

Forum statistics

Threads
1,077,827
Messages
5,336,611
Members
399,092
Latest member
jbwatkins

Some videos you may like

This Week's Hot Topics

Top