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

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.

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

@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?

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

@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

Maybe:

Please Login or Register  to view this content.

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

@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

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

...deleted...
Errors in code

Been searching the web to no avail...Really wanna see this play out...

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.

Thanks xladept
- will test tomorrow

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.

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.

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.

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

Hi Tjaart,

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

@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

What if the user does not use Ctrl+C to copy?

any suggestions?

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!

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!

Originally Posted by kev_

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!

You're welcome and thanks for the rep!