"Marching ants" around copied cell

[Simon Lloyd]

T. Valko, thanks for sticking with this discussion, i've enjoyed it!
rether than a button (sort of) and getting back to the personal.xls ho
about using this (i know we no longer use Autpen etc but it serves
purpose here), all the code below can go in a standard modlue in th
personal.xls, i assume that it will create the new menu item and work a
planned, not tested (im using 2007 now and i don't like it!)

Code
-------------------
Sub Auto_Close(Cancel As Boolean
On Error Resume Nex
Application.CommandBars("worksheet Menu Bar").Controls("Kill The Ants").Delet
On Error GoTo
End Su

Sub Autpen(
With Applicatio
.CommandBars.ActiveMenuBar.Enabled = Tru
For Each c In .CommandBars("Worksheet menu Bar").Control
If c.Caption = "Kill The Ants" Then c.Delet
Next
Set cb = .CommandBars("Worksheet Menu Bar").Controls.Add(Type:=msoControlButton, temporary:=True, ID:=2950, before:=1
cb.Caption = "Kill The Ants
cb.TooltipText = "Remove dotted line after paste
cb.OnAction = ("!KillAnts"
cb.Style = msoButtonCaptio
End Wit
End Su

Sub KillAnts(
Application.CutCopyMode = Fals
End Su

-------------------

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

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

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

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

-
Bif
Microsoft Excel MV



Cage Forums' (http://www.thecodecage.com/forumz/showthread.php?t=50724)

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[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

 

[Riccol]

Thanks! I'm going to print this out and give it a try this afternoon.

RC

 

[Riccol]

OK, cool, got it working, I now have an ant-killer button on my toolbar.
Thanks for taking the time to write up your instructions for me.
The only step I had trouble with was this one:

Goto the menu Window>Hide

This will hide the file.

Now, save the file:

Goto the menu File>Save As

In that order, my save options were all grayed-out. So I saved it first,
then did Window/Hide, and then it let me save it.

After getting the macro/button working, it took me sometime to figure
out how to modify the button, as the "modify" button was always grayed
out. I finally figured out that after right-clicking on the button on
the toolbar, and choosing "customize" from the list that opens, you have
to click on the button in the toolbar again to "activate" it for
modifying. And it doesn't matter what's selected in the customize dialog
box, you just have to open it and then click on the button on the
toolbar you want to modify. Took me a while to figure that out.

But after I figured that out, I figured out how to use whatever image I
want for the button instead of being limited to the ones in the "change
button" box.

You need a 16 x 16 .bmp image. Save it anywhere on your computer.
Pick any cell on whatever sheet you have open and choose "Insert -
Picture - From File". Navigate to your icon and select it for insertion.
Then copy it. (Make sure you're copying the picture and not the cell. Do
that by clicking on the picture inside of the cell instead of just
clicking on the cell, then right-click, "copy".)
With the image copied, go back to where you can modify your button and
instead of choosing "edit button" or "change button", choose "paste
button image". Bingo! You've now got your own personal image for your
button. (Don't forget to go back to the cell you inserted the image in
and delete the image from it. Inserting the image into a cell first was
the only way I could figure out how to copy it to the clipboard for
pasting as a button; there may be a better way to get it copied but I
couldn't find one.)

It's pretty hard making a 16x16 image. I wanted to do an ant image with
the no symbol over it, but 16x16 is a pretty small canvas! So I settled
for a simple "AK" button. It's here if you want to try personalizing
your button. http://users.rcn.com/switch32/AntKiller.bmp

Also, small "glitch". It won't let me put the button anywhere I want it.
The only place it will let me drag it to is to the right end of the main
toolbar at the top of the window (File, View, Help, etc..). Is that
normal or should I be able to place it somewhere else?

RC

 

[T. Valko]

Good job on getting a button face you want. MS has a whole collection of
button faces but they're not included with Excel. You can get them but it's
a lot more work and adds more complications to something that should be
pretty simple.

As far as placing the button where you want, you should be able to place it
anywhere you want on any existing toolbar. I have a group of custom buttons
on the standard toolbar between the sorting buttons and the zoom control.

A lot of folks would consider this overkill but for me it's an ergonomics
"thing"!

Thanks for the feedback!

 

[Robert Oppenheim]

Thanks a million.

Recently got a tablet computer (ASUS EP121) and I REALLY missed quick easy access to the ESC key. Calling up the onscreeen keyboard for this was a pain (click to call it up, then have to find exactly where the keyboard pop'd up, then click ESC then close the keyboard).

A tablet interfere needs more mouse and less keyboard.

FYI, I love my EP121, and this will make it even better.

-Rob

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

On Thursday, January 15, 2009 11:10 AM Andy Pope wrote:
Hi,

You can press the ESC button.

Cheers
Andy

<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