"Marching ants" around copied cell

[Riccol]

I know this is going to sound dumb but this really bugs me.

When I copy a cell(s), the copied area is highlighted with "marching
ants". Seems that after pasting, the "marching ants" around the source
area should go away, but it doesn't. The only way I can get rid of the
"marching ants" around the source is to click like a madwoman in a cell
adjacent to the ants. Sometimes when I do that though, the clicking goes
awry and I get stuck in a tangle that is very hard to explain, but the
only way out is to close the workbook without saving and then re-open it.

So my question is, what's the right way to get rid of the ants
highlighting the source area after copying?

(Excel 2002)

RC

 

[Andy Pope]

Hi,

You can press the ESC button.

Cheers
Andy

 

[T. Valko]

I know this is going to sound dumb but this really bugs me.

You're not the only one!

I know I can just hit the Escape key but I use the mouse extensively so I
went so far as to create a button on one of my toolbars that kills the
marching ants! It's a lot faster for me just to use the mouse.

 

[Riccol]

Thanks, Andy. Hitting Escape key quickly kills the ants without having
to click on a cell. I wonder why that little gem isn't in the Help file.
Thanks, I never would have thought to try Escape.

 

[Riccol]

How did you do that? I can see the benefit of killing them with the
mouse rather than having to reach for the Escape key.

 

[Gord Dibben]

Sub Escape()
SendKeys "{ESC}"
End Sub

Alternative..........

Sub Ant_Killer()
Application.CutCopyMode = False
End Sub


Gord Dibben MS Excel MVP

 

[T. Valko]

I use the "Ant_Killer" method!

To the OP:

Do you use macros for anything and if so do you have any of them stored in a
file called Personal.xls?

 

[Suleman Peerzade]

once you select the cells to be copied and press ctrl+C, you put the cursor
in cell where in you require the data to be pasted, now instead of pressing
Ctrl+V just press enter key and the data would get pasted and also the
marching ants will vanish away without you doing anything else.

If this is a repeated thing for eg. you want to paste the same data two
times then for the first time use ctrl+V and the second time you directly hit
the enter key. This would again do the same thing paste the required data in
the required cell and remove the marching ants without you doing anything
else.

 

[Riccol]

I've never used a Macro before. The spreadsheets I make are mostly just
invoices and ledger stuff for my small (very small) business, no real
complicated formulas or anything, thus I've never tried learning about
Macros.

So the codes posted to kill the ants are greek to me, I don't even know
where I'm supposed to type them. But I'd like to learn what to do with
them if anyone has the time to explain it.

Thanks everyone for your suggestions.

RC

 

[Riccol]

Thanks, Simon. I'm going to tackle your instructions in the AM and
report back after. (Your method sounds especially good as you say it
kills the ants immediately after pasting.)

RC

 

[Gord Dibben]

The macros provided by Biff and myself are just manually run macros and
should be copied into a General/Standard module, not event type code which
would go into Thisworkbook or a worksheet module.

If you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or Ron de De Bruin's site on where to store macros.

http://www.rondebruin.nl/code.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.

I would recommend not using the SendKeys macro.........SendKeys can be a
little flaky. I've never noticed but better safe than sorry.


Gord Dibben MS Excel MVP

 

[T. Valko]

The method that Simon is describing can only be used in the workbook in
which you place the code.

The method I use (which takes more work to setup) can be used in any
workbook.

 

[T. Valko]

Would that be the same if the code was saved in PERSONAL.xls?

???

I'm not following you.

 

[T. Valko]

Doesn't work that way for me. It only works in the Personal.xls file.

 

[T. Valko]

The *.xlb file is a file that stores information about toolbars and menus.

What you could do is create a new default book.xlt template and put the code
in there. Save the book.xlt file in the Excel startup directory. Then every
*new file* will have the code available but already existing files won't
have it.

Eh, that doesn't sound too "cool"! I'm sure you'd want to have this
available to *every* file. The only way I know how to do that is what I've
done and create a toolbar button and attach the macro.

It's more of an "ergonomics" issue with me. It's easier to use the mouse to
click the button then to have to look away to the keyboard and "find" the
ESC key!

 

[T. Valko]

I didn't test it. I don't like people "messing" with my toolbars! <g>

Just a thought....

Why go to the trouble of adding a new menu item for a general purpose task
on open and then deleting the same item on close? If it was a specialized
item that was only needed in specific files that would make sense. Since
this is something you'd want to be able to do in every file it doesn't make
sense to add the item on open and then delete it on close everytime you
start Excel.

I still think a simple toolbar button with an attached macro is the way to
go but I'm open to something better that isn't overly complicated.

 

[Riccol]

OK. First I tried Gordon's method:

<snip>
Sub Ant_Killer()
Application.CutCopyMode = False
End Sub

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code [given above] in there.
Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.
</snip>

I had no luck with that. Tried it twice from scratch and the ants remain
after pasting, then disappear in the process of clicking thru
Tools/Macros/Macros (or pressing Alt+F8 to open the macro window). So I
must be missing something. I didn't see the point of setting up the
macro when just trying to run the macro does the job the macro was set
up to do.

Next I tried Simon's method of pasting

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.CutCopyMode = False
End Sub

into ThisWorkbook.

That worked great, except if you want to paste the same source formula
in multiple cells you have to recopy the source each time, paste,
recopy, paste, recopy ... So this one actually works too well.

T.Valko/Biff's Toolbar method that'll work on all files - I must have
missed it because I didn't see that method actually explained anywhere
to try it.

So, I think Andrew's Escape method or Suleman's Enter method will work
best for me as I can paste multiple times then just hit Escape or Enter
after making the last paste. Escape is easier for me to reach on the
keyboard, so that will probably be the one that works its way into my
habits.

Thankyou everyone,

RC

 

[T. Valko]

T.Valko/Biff's Toolbar method that'll work on all files - I must have

missed it because I didn't see that method actually explained anywhere to
try it.

I'll write up an explanation later this evening when I have more free time.


--
Biff
Microsoft Excel MVP

OK. First I tried Gordon's method:

<snip>
Sub Ant_Killer()
Application.CutCopyMode = False
End Sub

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code [given above] in there.
Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.
</snip>

I had no luck with that. Tried it twice from scratch and the ants remain
after pasting, then disappear in the process of clicking thru
Tools/Macros/Macros (or pressing Alt+F8 to open the macro window). So I
must be missing something. I didn't see the point of setting up the macro
when just trying to run the macro does the job the macro was set up to do.

Next I tried Simon's method of pasting

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Application.CutCopyMode = False
End Sub

into ThisWorkbook.

That worked great, except if you want to paste the same source formula in
multiple cells you have to recopy the source each time, paste, recopy,
paste, recopy ... So this one actually works too well.

T.Valko/Biff's Toolbar method that'll work on all files - I must have
missed it because I didn't see that method actually explained anywhere to
try it.

So, I think Andrew's Escape method or Suleman's Enter method will work
best for me as I can paste multiple times then just hit Escape or Enter
after making the last paste. Escape is easier for me to reach on the
keyboard, so that will probably be the one that works its way into my
habits.

Thankyou everyone,

RC

 

[T. Valko]

Ok, I got side tracked yesterday...

Here goes...

Start Excel
Open the VBE editor ALT F11
Open the Project Explorer CTRL R

In the pane that opens on the left locate the file name. Since this is a new
file it doesn't yet have a saved name but should be listed as VBAProject
(Book1)

Right click on VBAProject (Book1) and select Insert>Module

Copy/paste this code into the right side window that opens:

Sub KillAnts()

Application.CutCopyMode = False

End Sub

Close the window and return to Excel

Goto the menu Window>Hide

This will hide the file.

Now, save the file:

Goto the menu File>Save As

File name: Personal.xls

Save In:

Save in your XLSTART directory which is typically located at:

C:\Documents and Settings\User\Application Data\Microsoft\Excel\XLSTART

Close Excel

Wait a minute or two and restart Excel

Now, create a toolbar button

Right click on any toolbar

Select the Commands tab>Macros

Select the "smilie face" button. Drag it to a good location on one of your
toolbars.

When you get it placed where you want it right click on it (make sure the
Customize user form is still open). You'll be presented with a menu of
various commands.

Select Assign Macro

A user form will open and you should see the macro KillAnts listed. Select
that macro and click on OK

You may want to change the button face to something other than a "smilie
face". Right click the button and in the menu you'll see a couple of
options: Edit Button Image or Change button Image. The Change Button Image
selections aren't too great. So, if you don't want any of those use the Edit
Button Image option. This too, isn't great but you can play around with it
until you get something you can "tolerate".

After your done close the Customize user form.

You should be good to go! Now, when you copy just click the new button you
just created and the marching ants will disappear. This will be available in
any file when you're using Excel.

--
Biff
Microsoft Excel MVP

T.Valko/Biff's Toolbar method that'll work on all files - I must have
missed it because I didn't see that method actually explained anywhere to
try it.

I'll write up an explanation later this evening when I have more free
time.


--
Biff
Microsoft Excel MVP

OK. First I tried Gordon's method:

<snip>
Sub Ant_Killer()
Application.CutCopyMode = False
End Sub

To create a General Module, hit ALT + F11 to open the Visual Basic
Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code [given above] in there.
Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.
</snip>

I had no luck with that. Tried it twice from scratch and the ants remain
after pasting, then disappear in the process of clicking thru
Tools/Macros/Macros (or pressing Alt+F8 to open the macro window). So I
must be missing something. I didn't see the point of setting up the macro
when just trying to run the macro does the job the macro was set up to
do.

Next I tried Simon's method of pasting

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Application.CutCopyMode = False
End Sub

into ThisWorkbook.

That worked great, except if you want to paste the same source formula in
multiple cells you have to recopy the source each time, paste, recopy,
paste, recopy ... So this one actually works too well.

T.Valko/Biff's Toolbar method that'll work on all files - I must have
missed it because I didn't see that method actually explained anywhere to
try it.

So, I think Andrew's Escape method or Suleman's Enter method will work
best for me as I can paste multiple times then just hit Escape or Enter
after making the last paste. Escape is easier for me to reach on the
keyboard, so that will probably be the one that works its way into my
habits.

Thankyou everyone,

RC

 

[T. Valko]

Ooops!

Missed a step:

Right click on any toolbar
Select the Commands tab>Macros

Should be:

Right click on any toolbar
Select Customize
Select the Commands tab>Macros


--
Biff
Microsoft Excel MVP

Ok, I got side tracked yesterday...

Here goes...

Start Excel
Open the VBE editor ALT F11
Open the Project Explorer CTRL R

In the pane that opens on the left locate the file name. Since this is a
new file it doesn't yet have a saved name but should be listed as
VBAProject (Book1)

Right click on VBAProject (Book1) and select Insert>Module

Copy/paste this code into the right side window that opens:

Sub KillAnts()

Application.CutCopyMode = False

End Sub

Close the window and return to Excel

Goto the menu Window>Hide

This will hide the file.

Now, save the file:

Goto the menu File>Save As

File name: Personal.xls

Save In:

Save in your XLSTART directory which is typically located at:

C:\Documents and Settings\User\Application Data\Microsoft\Excel\XLSTART

Close Excel

Wait a minute or two and restart Excel

Now, create a toolbar button

Right click on any toolbar

Select the Commands tab>Macros

Select the "smilie face" button. Drag it to a good location on one of your
toolbars.

When you get it placed where you want it right click on it (make sure the
Customize user form is still open). You'll be presented with a menu of
various commands.

Select Assign Macro

A user form will open and you should see the macro KillAnts listed. Select
that macro and click on OK

You may want to change the button face to something other than a "smilie
face". Right click the button and in the menu you'll see a couple of
options: Edit Button Image or Change button Image. The Change Button Image
selections aren't too great. So, if you don't want any of those use the
Edit Button Image option. This too, isn't great but you can play around
with it until you get something you can "tolerate".

After your done close the Customize user form.

You should be good to go! Now, when you copy just click the new button you
just created and the marching ants will disappear. This will be available
in any file when you're using Excel.

--
Biff
Microsoft Excel MVP

T.Valko/Biff's Toolbar method that'll work on all files - I must have
missed it because I didn't see that method actually explained anywhere
to try it.

I'll write up an explanation later this evening when I have more free
time.


--
Biff
Microsoft Excel MVP

OK. First I tried Gordon's method:

<snip>
Sub Ant_Killer()
Application.CutCopyMode = False
End Sub

To create a General Module, hit ALT + F11 to open the Visual Basic
Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code [given above] in there.
Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.
</snip>

I had no luck with that. Tried it twice from scratch and the ants remain
after pasting, then disappear in the process of clicking thru
Tools/Macros/Macros (or pressing Alt+F8 to open the macro window). So I
must be missing something. I didn't see the point of setting up the
macro when just trying to run the macro does the job the macro was set
up to do.

Next I tried Simon's method of pasting

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Application.CutCopyMode = False
End Sub

into ThisWorkbook.

That worked great, except if you want to paste the same source formula
in multiple cells you have to recopy the source each time, paste,
recopy, paste, recopy ... So this one actually works too well.

T.Valko/Biff's Toolbar method that'll work on all files - I must have
missed it because I didn't see that method actually explained anywhere
to try it.

So, I think Andrew's Escape method or Suleman's Enter method will work
best for me as I can paste multiple times then just hit Escape or Enter
after making the last paste. Escape is easier for me to reach on the
keyboard, so that will probably be the one that works its way into my
habits.

Thankyou everyone,

RC