Results 1 to 9 of 9

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

  1. #1
    New Member
    Join Date
    Sep 2019
    Location
    Morocco
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    Board Regular pbornemeier's Avatar
    Join Date
    May 2005
    Location
    Virginia Beach, VA USA
    Posts
    3,659
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

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

    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.
    Phil

    - Use CODE tags to keep your code formatted. See: BB Tags
    - How to attach Screenshots
    - Try searching for your answer first, see how
    - Test and validate results for all code on a copy of your worksheet!! How do you use the code you just found?
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

  3. #3
    New Member
    Join Date
    Sep 2019
    Location
    Morocco
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  4. #4
    Board Regular pbornemeier's Avatar
    Join Date
    May 2005
    Location
    Virginia Beach, VA USA
    Posts
    3,659
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

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

    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.
    Phil

    - Use CODE tags to keep your code formatted. See: BB Tags
    - How to attach Screenshots
    - Try searching for your answer first, see how
    - Test and validate results for all code on a copy of your worksheet!! How do you use the code you just found?
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

  5. #5
    New Member
    Join Date
    Sep 2019
    Location
    Morocco
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

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

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

  6. #6
    Board Regular pbornemeier's Avatar
    Join Date
    May 2005
    Location
    Virginia Beach, VA USA
    Posts
    3,659
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

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

    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
    Phil

    - Use CODE tags to keep your code formatted. See: BB Tags
    - How to attach Screenshots
    - Try searching for your answer first, see how
    - Test and validate results for all code on a copy of your worksheet!! How do you use the code you just found?
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

  7. #7
    New Member
    Join Date
    Sep 2019
    Location
    Morocco
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  8. #8
    Board Regular pbornemeier's Avatar
    Join Date
    May 2005
    Location
    Virginia Beach, VA USA
    Posts
    3,659
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

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

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

    - Use CODE tags to keep your code formatted. See: BB Tags
    - How to attach Screenshots
    - Try searching for your answer first, see how
    - Test and validate results for all code on a copy of your worksheet!! How do you use the code you just found?
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

  9. #9
    New Member
    Join Date
    Sep 2019
    Location
    Morocco
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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