Range copy method causes Right Click menu to disappear RRS feed

  • Question

  • I am trying to setup an audit trail for changes to any spreadsheet in the workbook. Capturing changes and posting them to an AuditLog worksheet is easy, but now I'm trying to capture any deleted rows too. That is a BIT more challenging.

    To do this, I am copying the current row(s) any time the selection changes on a worksheet, to a worksheet called AuditTemp. That way, if something is then deleted, I can copy that entire row to the AuditTrail worksheet. The code I use to do this is below. It is called from the Workbook_SheetSelectionChange event. The actual code to write the changes to AuditLog is called from the Workbook_SheetChange event.

    So far, if an entire row is selected (clicking on the row number to the left of the data) everything works fine. Even if multiple rows are selected, it works fine and the data on those rows are copied to the AuditTemp worksheet (with column headings as comments in each cell).

    The problem is, if I right click on the row number (to select DELETE for example) the right-click menu pops up but then goes away before I can do anything. I tracked it down (by putting Exit Sub in front of different statements below) to the Copy method (rngCopy.Copy rngDest) in the code below. If that statement is not executed, the right-click menu is fine. I confirmed that by then commenting out that one line and the menu was fine.

    Any ideas why this happens or how to get around it?

    The other interesting thing about this is if I right-click on the row (in the data area, not on the row header) the right-click menu is fine so there is a work-around, but I don't understand the problem and don't like to have to use it that way.

    Public Sub SaveRowInfo(ByVal wksCurrent As Object, _
                           ByVal rngTarget As Range)
        Dim rngCopy As Range, _
            rngDest As Range, _
            rngCell As Range
        Dim wksTemp As Worksheet
        ' Setup a local version of the AuditTemp worksheet
        Set wksTemp = ActiveWorkbook.Worksheets(AUDIT_TEMP)
        ' Get the set of cells to copy by getting the intersection of the entire row and the used range for the rngTarget row
        Set rngCopy = Application.Intersect(rngTarget.EntireRow, RangeUsed(rngTarget.Worksheet))
        ' Clear the used range on the temporary worksheet to prepare for the copy of the current rngTarget
        Set rngDest = RangeUsed(wksTemp)
        ' Set the destination for the copy location.
        Set rngDest = wksTemp.Range("A1")
        ' Copy the current row's data to the destination on the temporary worksheet
        rngCopy.Copy rngDest
        ' Loop through all the cells on the temporary worksheet and add a comment to get their corresponding headers
        Set rngCopy = RangeUsed(wksTemp)
        For Each rngCell In rngCopy.Cells
            rngCell.Comment.Visible = False
            rngCell.Comment.Text Text:=GetHeader(rngTarget.Worksheet.Range(rngCell.Address))
        Next rngCell
        ' If the rngTarget is a single cell, get the value of that cell
        If rngTarget.Cells.Count = 1 Then
            gstrPreviousValue = rngTarget.Value
            ' Multiple cells were selected, so note that as the previous value
            gstrPreviousValue = "<Range Selected>"
        End If
    End Sub


    Thursday, May 14, 2015 1:43 PM

All replies

  • Hi Larry,

    According to the description, the context menu disapear when you right click the row num.

    Based on my understanding, right click the row num wouldn't fire worksheet change event. Did it work in a new workbook?

    If yes, would you mind share a demo workbook to help us to reproduce this issue. Since I am not able to reproduce this issue and can't find the function for RangeUsed.

    If not, I suggest that you try it in safe model to see whether this issue was cauesed by add-ins. If the issue still exits, I suggest that you try to update or repait the Offce to see whether it is helpful.

    Regards & Fei

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, May 15, 2015 2:15 AM