+ Reply to Thread
Results 1 to 27 of 27

Get Marching Ants Range

  1. #1
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Get Marching Ants Range

    User selects a range and hits {CTRL} c
    - the range to be copied is surrounded by the familiar marching ants

    What is the VBA to return that range?
    (either as string or range)

    thank you



    TO CLARIFY

    I want to capture the "marching ants" range even if user has moved to another cell
    - "marching ants" still remain around the original range
    Last edited by kev_; 12-23-2017 at 03:20 AM.
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Get Marching Ants Range

    One and lazy way is to use current region- provided there are complete blank rows or columns.

    Range("A1").CurrentRegion.Select

  3. #3
    Valued Forum Contributor
    Join Date
    03-16-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    371

    Re: Get Marching Ants Range

    If the marching ants range is no longer selected, it is trickier than you might think. I had the same question a while back... unfortunately priorities did not allow me to solve the problem. Hopefully I can give you some clues. I'll be subscribing to see if this specific problem is solved

    My old thread:
    https://www.excelforum.com/excel-pro...clipboard.html

    Some code I've found but not tested at all (sorry can't remember where I got it from so cannot credit the writer):
    Please Login or Register  to view this content.
    Design everything to be as simple as possible, but no simpler.

  4. #4
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Get Marching Ants Range

    Thanks @AB33
    - the range could be any range, not necessarily the CurrentRegion

    Thanks @storming
    - I will find a way to do this ...just hoping someone else already got there...

  5. #5
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Get Marching Ants Range

    How about

    UsedRange.Select

    ?

  6. #6
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Get Marching Ants Range

    @AB33 - it is not all cells - it could be any range in the worksheet
    - Example
    A1 to Z20 as range with data values
    User selects B2 to B5 and hits {ctrl} c
    B2 to B5 is now surrounded by marching ants
    User clicks on Z2
    Marching ants still surround B2 to B5
    - how to use VBA to get that range?
    Last edited by kev_; 12-22-2017 at 02:33 PM.

  7. #7
    Valued Forum Contributor
    Join Date
    03-16-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    371

    Re: Get Marching Ants Range

    You will most likely have to retrieve the range somehow from the clipboard. Since the 'marching ants' range can be pasted to external applications it is not stored as an object in Excel but rather in the Windows clipboard.

    I did some more GoogleFu, hopefully there is some information and/or ideas in these links that can help you. Look forward to hearing about any progress!

    https://stackoverflow.com/a/23119068

    https://stackoverflow.com/a/12117460

    http://www.ozgrid.com/forum/showthread.php?t=66773

  8. #8
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Get Marching Ants Range

    @stormin'
    Thanks for the various links etc.
    With Christmas getting in the way, this may take a few days, but I will update the thread with any progress I make
    Merry Christmas

  9. #9
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Get Marching Ants Range

    Maybe:

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  10. #10
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Get Marching Ants Range

    ...........Deleted...........
    Stormin' already added links

    Edit
    Previously deleted...
    Std Module
    Please Login or Register  to view this content.
    Sheet Module
    Please Login or Register  to view this content.
    Last edited by sintek; 12-27-2017 at 10:00 AM.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  11. #11
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Get Marching Ants Range

    @xladept - thanks
    - your suggestion works if the area surrounded by the marching ants is still the active cells
    - but it does not work after the user has clicked on another cell

  12. #12
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Get Marching Ants Range

    @sintek - thanks
    your suggestion should help push things in the correct direction
    - but as it stands it is not retaining the marching ants range
    - SaveRange should retain marching ants range until Application.CutCopyMode goes back to False
    - this should be fixable...
    - will report back later...

  13. #13
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Get Marching Ants Range

    ...deleted...
    Errors in code

    Been searching the web to no avail...Really wanna see this play out...
    Last edited by sintek; 12-23-2017 at 01:34 PM.

  14. #14
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Get Marching Ants Range

    Put this in a standard module all it's own run SetKey once and let me know:

    Please Login or Register  to view this content.

  15. #15
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Get Marching Ants Range

    Thanks xladept
    - will test tomorrow

  16. #16
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Get Marching Ants Range

    What about pasting range elsewhere and then searching?...Suppose can only work with unique values...
    If not unique then incorporate code to see if range is horizontal or vertical and check for offset matching values...Unorthodox perhaps...Any other method escapes me.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by sintek; 12-24-2017 at 08:55 AM.

  17. #17
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Get Marching Ants Range

    I think I am now very close to solving this (...but... Christmas interuppting progress at the moment..)

    the code below:
    - retains the marching ants range until {ESC} is clicked (exactly what I require)
    - UNFORTUNATELY if user hits {CTRL} c a second time, without hitting {ESC}, then the first marching ants range is retained

    to get the total solution:
    - the code below needs merging with xladept code in post#14
    - I expect that to provide the total solution

    @xladept
    - any ideas on best way to merge your code with mine?
    - I want {CTRL} c to be the trigger (your code) AND to retain the range until either cancelled {ESC} OR {CTRL} c hit again

    general module:
    Please Login or Register  to view this content.
    sheet module:
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by kev_; 12-24-2017 at 03:16 PM.

  18. #18
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Get Marching Ants Range

    I deleted the sheet event code and this seems to be working: Run SetKey once then try:

    Please Login or Register  to view this content.
    Last edited by xladept; 12-24-2017 at 04:13 PM.

  19. #19
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Get Marching Ants Range

    @ xladept,

    Hi there xladept,

    will the tweak below not solve the Uppercase for you?

    Please Login or Register  to view this content.
    Of course you could also consider adding the "Option Explicit" route as well.

    Regards.
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  20. #20
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Get Marching Ants Range

    Hi Tjaart,

    In this case I'm overloading the Excel ^c for copy selection.

  21. #21
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Get Marching Ants Range

    @xladept & @sintek THANK YOU!!

    With a small modification I have now been able to get the code to do exactly what I wanted

    I was trying to solve another thread where OP wanted to prevent copy & paste overwriting data validation in cells

    The marching ants was first step in the solution:
    1. determine the marching ants range
    2. (based on current active cell) determine PasteTo range
    3. determine if the PasteTo range overlapped any DataValidation cells
    4. if an overlap occurs then cancel copy to prevent overwrite

    I have posted the solution on post#14 of the other thread
    Last edited by kev_; 12-27-2017 at 08:26 AM.

  22. #22
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Get Marching Ants Range

    What if the user does not use Ctrl+C to copy?
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  23. #23
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Get Marching Ants Range

    any suggestions?

  24. #24
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Get Marching Ants Range

    Since the source range address is not, so far as I know, part of the information stored on the clipboard anywhere, I would approach the problem from the other end. Monitor the selected range to see if any of the cells have validation and set a flag accordingly. Then if any changes are made, store the current values, undo the change, and then validate the values against the current rules. Or for a simpler version, merely clear the clipboard if the selection contains validation cells.

    In reality, instead of any of the above, I would suggest that better business controls should be in place. Deliberately attempting to add invalid data to business systems should be a disciplinary offence!

  25. #25
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Get Marching Ants Range

    I agree with your sentiments!!

    The real problem though is the weakness of DV as a control
    - user can inadvertently destroy the DV by copy pasting
    It should be made possible to protect DV without convoluted VBA and without sheet protection
    - in current form it gives a false sense of security!

  26. #26
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Get Marching Ants Range

    Quote Originally Posted by kev_ View Post
    It should be made possible to protect DV without convoluted VBA and without sheet protection
    - in current form it gives a false sense of security!
    I totally agree with both of those statements!

  27. #27
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Get Marching Ants Range

    You're welcome and thanks for the rep!

+ 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. [SOLVED] Remove 'marching ants' from copied cell
    By staggers47 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-14-2017, 12:26 AM
  2. Copy and Paste no longer showing the "marching ants."
    By Neener in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 11-01-2014, 07:08 AM
  3. Remove marching ants after notepad macro opens notepad
    By swade730 in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 01-07-2014, 09:47 PM
  4. Replies: 9
    Last Post: 04-22-2012, 11:44 PM
  5. [SOLVED] Excel should include flashing cells/block and marching ants like W
    By waholt in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-21-2005, 02:05 PM
  6. Marching ants
    By news.microsoft.com in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-22-2005, 02:05 PM

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