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

Excel addicted

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

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

pbornemeier

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

Watch MrExcel Video

Forum statistics

Threads
1,095,189
Messages
5,442,937
Members
405,206
Latest member
Neetish_93

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top