+ Reply to Thread
Results 1 to 2 of 2

Range copy method causes Right Click menu to disappear

  1. #1
    Registered User
    Join Date
    03-27-2014
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2010
    Posts
    2

    Range copy method causes Right Click menu to disappear

    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.

    Please Login or Register  to view this content.

  2. #2
    Registered User
    Join Date
    03-27-2014
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Range copy method causes Right Click menu to disappear

    Well, I got around the "rngCopy.Copy Dest" problem by writing my own function to do the copy, instead of using the built-in method. Now the menu works and the data is copied as well. I have
    pasted the code below, calling this subroutine with the same objects that were being used in the previous copy method.

    BUT, I have hit another problem. If someone wants to copy a cell from one place to another, the data on the clipboard is lost when they move to the new cell and they can no longer paste.
    The selected cell is lost as well, which I think, is probably the real problem. So now I am trying to figure out how to make the Copy/Paste ability work.

    If anyone has thoughts, about this new problem or a better way to handle this in general, I would appreciate it.

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Right click cut, copy paste menu...
    By Chazrab in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-08-2015, 10:55 AM
  2. Replies: 1
    Last Post: 05-10-2014, 05:15 PM
  3. add button to right-click menu in External Data range
    By dlh in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 07-21-2010, 07:39 PM
  4. txtbox no right click copy menu available?
    By Craigm in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-24-2006, 11:33 AM
  5. [SOLVED] my menu disappear. How can I refind my menu??
    By Allen in forum Excel General
    Replies: 1
    Last Post: 01-26-2005, 08:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1