Ooops!

Missed a step:

Right click on any toolbar
Select the Commands tabMacros

Should be:

Right click on any toolbar
Select Customize
Select the Commands tabMacros


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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 InsertModule

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 WindowHide

This will hide the file.

Now, save the file:

Goto the menu FileSave 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 tabMacros

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" wrote in message
...
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


"Riccol" wrote in message
...
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 InsertModule. 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 ToolMacroMacros.

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