+ Reply to Thread
Results 1 to 8 of 8

Excel Drag and Drop - How to Undo Using VBA

  1. 02-20-2022, 03:16 PM #1
    CurtisD
    CurtisD is offline
    Registered User
    Join Date
    08-24-2019
    Location
    Munster, Indiana, USA
    MS-Off Ver
    Excel 365
    Posts
    20

    Excel Drag and Drop - How to Undo Using VBA

    I have an Excel sheet in which the user drags a cell in column A to any cell in column B. Each of the cells in column B has a random integer. If the user drags a cell in Column A to a cell in Column B that has an odd number I want to show a message and undo the operation. It's OK to drag and drop to a cell with an even integer but not an odd integer.

    Using VBA I can capture the address and value of the cell in Column A that is being dragged, and the address of the destination cell in Column B. But I don't know how to capture the initial value in Column B before it is replaced by the value that is dragged/dropped.

    Please let me know if it is possible to do either of these:

    - capture the value being replaced (so I can restore it if I wish to undo the drag and drop)

    - simply undo the drag and drop operation (restore the values in the two cells)

    Thanks.
    Reply With QuoteRegister To Reply
  2. 02-20-2022, 05:43 PM #2
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Excel Drag and Drop - How to Undo Using VBA

    Here's one way using the Sheet change event

    Please Login or Register  to view this content.
    Last edited by Richard Buttrey; 02-20-2022 at 05:49 PM.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
    Reply With QuoteRegister To Reply
  3. 02-20-2022, 06:21 PM #3
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,099

    Re: Excel Drag and Drop - How to Undo Using VBA

    @Richard: 
    Please Login or Register  to view this content.
    ?????
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  4. 02-20-2022, 06:55 PM #4
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Excel Drag and Drop - How to Undo Using VBA

    Thanks Trevor,

    I put that in at the last minute so that I could reset numbers in column B

    Until then it seemed that in selecting and dragging across from column A to Column B the 'Target' was being treated as two cells.
    However now I've checked again it's only one so I agree it seems superfluous.

    I'm puzzled now as to how I was encountering a count of two ....
    Reply With QuoteRegister To Reply
  5. 02-20-2022, 06:57 PM #5
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,099

    Re: Excel Drag and Drop - How to Undo Using VBA

    I thought you might have meant not equals for, for example, deleting multiple cells.
    Reply With QuoteRegister To Reply
  6. 02-20-2022, 07:07 PM #6
    CurtisD
    CurtisD is offline
    Registered User
    Join Date
    08-24-2019
    Location
    Munster, Indiana, USA
    MS-Off Ver
    Excel 365
    Posts
    20

    Re: Excel Drag and Drop - How to Undo Using VBA

    Thank you, Richard and Trevor . I will test out your code.
    Reply With QuoteRegister To Reply
  7. 02-20-2022, 07:32 PM #7
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Excel Drag and Drop - How to Undo Using VBA

    Quote Originally Posted by CurtisD View Post
    Thank you, Richard and Trevor . I will test out your code.
    You will need to delete the Exit Sub line at the top.
    Reply With QuoteRegister To Reply
  8. 02-20-2022, 08:00 PM #8
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,099

    Re: Excel Drag and Drop - How to Undo Using VBA

    You're welcome. Thanks for the rep.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to Control Drag and Drop? (force "shift" drag-drop, disable into certain rows)
    By superlative in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-18-2019, 11:16 AM
  2. Drag&drop outlook email to drop zone in excel
    By mpx300 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-02-2015, 05:00 PM
  3. drag and drop from Excel
    By MLC2 in forum Access Tables & Databases
    Replies: 3
    Last Post: 10-12-2012, 02:04 PM
  4. Excel drag & drop
    By daniel123 in forum Excel General
    Replies: 5
    Last Post: 11-10-2009, 05:43 PM
  5. Drag and drop Web services in Excel
    By sibob in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-15-2006, 01:50 PM
  6. [SOLVED] My undo routine fails with drag and drop.
    By serdar in forum Excel General
    Replies: 5
    Last Post: 06-12-2005, 02:05 PM
  7. Drag Drop in Excel
    By rahul.singh in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-24-2005, 12:35 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1