Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 21
Default "Marching ants" around copied cell

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
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: "Marching ants" around copied cell

Hi RC,

I completely understand how those "marching ants" can be annoying! Luckily, there is a simple solution to get rid of them.

After you have pasted the copied cell(s), simply press the "Esc" key on your keyboard. This will deselect the copied area and remove the "marching ants" from the source area.

Alternatively, you can also click on any other cell in the worksheet to deselect the copied area and remove the "marching ants".
  1. Copy the cell(s)
  2. Paste the copied cell(s)
  3. Press the "Esc" key or click on any other cell to deselect the copied area and remove the "marching ants"
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2,489
Default "Marching ants" around copied cell

Hi,

You can press the ESC button.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Riccol" wrote in message
...
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


  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default "Marching ants" around copied cell

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.

--
Biff
Microsoft Excel MVP


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



  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 21
Default "Marching ants" around copied cell

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.

Andy Pope wrote:
Hi,

You can press the ESC button.

Cheers
Andy



  #6   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 21
Default "Marching ants" around copied cell

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.

T. Valko wrote:

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.

  #7   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 22,906
Default "Marching ants" around copied cell

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

Alternative..........

Sub Ant_Killer()
Application.CutCopyMode = False
End Sub


Gord Dibben MS Excel MVP

On Thu, 15 Jan 2009 13:59:18 -0500, Riccol wrote:

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.

T. Valko wrote:

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.


  #8   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default "Marching ants" around copied cell

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?

--
Biff
Microsoft Excel MVP


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Sub Escape()
SendKeys "{ESC}"
End Sub

Alternative..........

Sub Ant_Killer()
Application.CutCopyMode = False
End Sub


Gord Dibben MS Excel MVP

On Thu, 15 Jan 2009 13:59:18 -0500, Riccol wrote:

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.

T. Valko wrote:

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.




  #9   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1
Default "Marching ants" around copied cell


The simplest way that requires no extra movement is to put this in the
ThsiWorkbook module:
Code:
--------------------
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.CutCopyMode = False
End Sub
--------------------
right after you have pasted it "kills" the ants!Riccol;183577 Wrote:
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



--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=50724

  #10   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 189
Default "Marching ants" around copied cell

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.
--
Thanks
Suleman Peerzade


"Riccol" wrote:

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



  #11   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 21
Default "Marching ants" around copied cell

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

T. Valko wrote:
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?

  #12   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1
Default "Marching ants" around copied cell


Press and hold Alt & F11, the VBE (visual basic editor) will open, on
the left you will see a list of your worksheets and on called
ThisWorkbook, they are all code modules, double click the Thisworkbook
and paste the code i gave.

If you are ever given code for a standard module then use Alt+F11 as
before and instead of double clicking Thisworkbook, right click in that
area, choose Insert and then choose Module, then you can paste your code
in there. The best way to learn is to record a macro while you are doing
a task and then go back and take a look at the generated code, it's true
to say that you will see a lot of code that is excess to requirements
but it will give you a basic idea on how things are structured.Riccol;186154 Wrote:
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

T. Valko wrote:
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?



--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=50724

  #13   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 21
Default "Marching ants" around copied cell

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

Simon Lloyd wrote:
Press and hold Alt & F11, the VBE (visual basic editor) will open, on
the left you will see a list of your worksheets and on called
ThisWorkbook, they are all code modules, double click the Thisworkbook
and paste the code i gave.

If you are ever given code for a standard module then use Alt+F11 as
before and instead of double clicking Thisworkbook, right click in that
area, choose Insert and then choose Module, then you can paste your code
in there. The best way to learn is to record a macro while you are doing
a task and then go back and take a look at the generated code, it's true
to say that you will see a lot of code that is excess to requirements
but it will give you a basic idea on how things are structured.

  #14   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 22,906
Default "Marching ants" around copied cell

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

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

On Fri, 16 Jan 2009 19:09:14 -0500, Riccol wrote:

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

T. Valko wrote:
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?


  #15   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default "Marching ants" around copied cell

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.

--
Biff
Microsoft Excel MVP


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

Simon Lloyd wrote:
Press and hold Alt & F11, the VBE (visual basic editor) will open, on
the left you will see a list of your worksheets and on called
ThisWorkbook, they are all code modules, double click the Thisworkbook
and paste the code i gave.

If you are ever given code for a standard module then use Alt+F11 as
before and instead of double clicking Thisworkbook, right click in that
area, choose Insert and then choose Module, then you can paste your code
in there. The best way to learn is to record a macro while you are doing
a task and then go back and take a look at the generated code, it's true
to say that you will see a lot of code that is excess to requirements
but it will give you a basic idea on how things are structured.





  #16   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1
Default "Marching ants" around copied cell


Would that be the same if the code was saved in PERSONAL.xls?T. Valko;186308 Wrote:
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.

--
Biff
Microsoft Excel MVP


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

Simon Lloyd wrote:
Press and hold Alt & F11, the VBE (visual basic editor) will open,

on
the left you will see a list of your worksheets and on called
ThisWorkbook, they are all code modules, double click the

Thisworkbook
and paste the code i gave.

If you are ever given code for a standard module then use Alt+F11 as
before and instead of double clicking Thisworkbook, right click in

that
area, choose Insert and then choose Module, then you can paste your

code
in there. The best way to learn is to record a macro while you are

doing
a task and then go back and take a look at the generated code, it's

true
to say that you will see a lot of code that is excess to

requirements
but it will give you a basic idea on how things are structured.



--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=50724

  #17   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default "Marching ants" around copied cell

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

???

I'm not following you.

--
Biff
Microsoft Excel MVP


"Simon Lloyd" wrote in message
...

Would that be the same if the code was saved in PERSONAL.xls?T.
Valko;186308 Wrote:
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.

--
Biff
Microsoft Excel MVP


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

Simon Lloyd wrote:
Press and hold Alt & F11, the VBE (visual basic editor) will open,

on
the left you will see a list of your worksheets and on called
ThisWorkbook, they are all code modules, double click the

Thisworkbook
and paste the code i gave.

If you are ever given code for a standard module then use Alt+F11 as
before and instead of double clicking Thisworkbook, right click in

that
area, choose Insert and then choose Module, then you can paste your

code
in there. The best way to learn is to record a macro while you are

doing
a task and then go back and take a look at the generated code, it's

true
to say that you will see a lot of code that is excess to

requirements
but it will give you a basic idea on how things are structured.



--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=50724



  #18   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1
Default "Marching ants" around copied cell


Sorry!, i meant if the code was stored in the ThisWorkbook module of
PERSONAL.xls would it not then affect every workbook opened?T. Valko;186343 Wrote:
Would that be the same if the code was saved in PERSONAL.xls?


???

I'm not following you.

--
Biff
Microsoft Excel MVP


"Simon Lloyd" wrote in message
...

Would that be the same if the code was saved in PERSONAL.xls?T.
Valko;186308 Wrote:
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.

--
Biff
Microsoft Excel MVP


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

Simon Lloyd wrote:
Press and hold Alt & F11, the VBE (visual basic editor) will

open,
on
the left you will see a list of your worksheets and on called
ThisWorkbook, they are all code modules, double click the
Thisworkbook
and paste the code i gave.

If you are ever given code for a standard module then use Alt+F11

as
before and instead of double clicking Thisworkbook, right click

in
that
area, choose Insert and then choose Module, then you can paste

your
code
in there. The best way to learn is to record a macro while you

are
doing
a task and then go back and take a look at the generated code,

it's
true
to say that you will see a lot of code that is excess to
requirements
but it will give you a basic idea on how things are structured.



--
Simon Lloyd

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

------------------------------------------------------------------------
Simon Lloyd's Profile:
'The Code Cage Forums - View Profile: Simon Lloyd'

(http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread: '\"Marching ants\" around copied cell - The Code

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



--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=50724

  #19   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default "Marching ants" around copied cell

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

--
Biff
Microsoft Excel MVP


"Simon Lloyd" wrote in message
...

Sorry!, i meant if the code was stored in the ThisWorkbook module of
PERSONAL.xls would it not then affect every workbook opened?T.
Valko;186343 Wrote:
Would that be the same if the code was saved in PERSONAL.xls?


???

I'm not following you.

--
Biff
Microsoft Excel MVP


"Simon Lloyd" wrote in message
...

Would that be the same if the code was saved in PERSONAL.xls?T.
Valko;186308 Wrote:
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.

--
Biff
Microsoft Excel MVP


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

Simon Lloyd wrote:
Press and hold Alt & F11, the VBE (visual basic editor) will

open,
on
the left you will see a list of your worksheets and on called
ThisWorkbook, they are all code modules, double click the
Thisworkbook
and paste the code i gave.

If you are ever given code for a standard module then use Alt+F11

as
before and instead of double clicking Thisworkbook, right click

in
that
area, choose Insert and then choose Module, then you can paste

your
code
in there. The best way to learn is to record a macro while you

are
doing
a task and then go back and take a look at the generated code,

it's
true
to say that you will see a lot of code that is excess to
requirements
but it will give you a basic idea on how things are structured.


--
Simon Lloyd

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

------------------------------------------------------------------------
Simon Lloyd's Profile:
'The Code Cage Forums - View Profile: Simon Lloyd'

(http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread: '\"Marching ants\" around copied cell - The Code

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



--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=50724



  #20   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1
Default "Marching ants" around copied cell


I hadn't tried it, i thought that all code stored in the PERSONAL.xls
was available in each workbook you open. This may be crude but how about
putting that Thisworkbook code in the .xlb file? as this is the base
file that excel references when starting (or at least thats what i
presume) wouldn't every workbook you open already be populated with that
code?

T. Valko;186952 Wrote:
Doesn't work that way for me. It only works in the Personal.xls file.

--
Biff
Microsoft Excel MVP


"Simon Lloyd" wrote in message
...

Sorry!, i meant if the code was stored in the ThisWorkbook module of
PERSONAL.xls would it not then affect every workbook opened?T.
Valko;186343 Wrote:
Would that be the same if the code was saved in PERSONAL.xls?

???

I'm not following you.

--
Biff
Microsoft Excel MVP


"Simon Lloyd" wrote in message
...

Would that be the same if the code was saved in PERSONAL.xls?T.
Valko;186308 Wrote:
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.

--
Biff
Microsoft Excel MVP


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

Simon Lloyd wrote:
Press and hold Alt & F11, the VBE (visual basic editor) will
open,
on
the left you will see a list of your worksheets and on called
ThisWorkbook, they are all code modules, double click the
Thisworkbook
and paste the code i gave.

If you are ever given code for a standard module then use

Alt+F11
as
before and instead of double clicking Thisworkbook, right

click
in
that
area, choose Insert and then choose Module, then you can paste
your
code
in there. The best way to learn is to record a macro while you
are
doing
a task and then go back and take a look at the generated code,
it's
true
to say that you will see a lot of code that is excess to
requirements
but it will give you a basic idea on how things are

structured.


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' ('The Code Cage' ('The Code Cage'

(http://www.thecodecage.com)))


------------------------------------------------------------------------
Simon Lloyd's Profile:
'The Code Cage Forums - View Profile: Simon Lloyd'
('The Code Cage Forums - View Profile: Simon Lloyd'

(http://www.thecodecage.com/forumz/member.php?userid=1))
View this thread: '\"Marching ants\" around copied cell - The Code
Cage Forums' ('\"Marching ants\" around copied cell - The Code Cage

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



--
Simon Lloyd

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

------------------------------------------------------------------------
Simon Lloyd's Profile:
'The Code Cage Forums - View Profile: Simon Lloyd'

(http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread: '\"Marching ants\" around copied cell - The Code

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



--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=50724



  #21   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default "Marching ants" around copied cell

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!

--
Biff
Microsoft Excel MVP


"Simon Lloyd" wrote in message
...

I hadn't tried it, i thought that all code stored in the PERSONAL.xls
was available in each workbook you open. This may be crude but how about
putting that Thisworkbook code in the .xlb file? as this is the base
file that excel references when starting (or at least thats what i
presume) wouldn't every workbook you open already be populated with that
code?

T. Valko;186952 Wrote:
Doesn't work that way for me. It only works in the Personal.xls file.

--
Biff
Microsoft Excel MVP


"Simon Lloyd" wrote in message
...

Sorry!, i meant if the code was stored in the ThisWorkbook module of
PERSONAL.xls would it not then affect every workbook opened?T.
Valko;186343 Wrote:
Would that be the same if the code was saved in PERSONAL.xls?

???

I'm not following you.

--
Biff
Microsoft Excel MVP


"Simon Lloyd" wrote in message
...

Would that be the same if the code was saved in PERSONAL.xls?T.
Valko;186308 Wrote:
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.

--
Biff
Microsoft Excel MVP


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

Simon Lloyd wrote:
Press and hold Alt & F11, the VBE (visual basic editor) will
open,
on
the left you will see a list of your worksheets and on called
ThisWorkbook, they are all code modules, double click the
Thisworkbook
and paste the code i gave.

If you are ever given code for a standard module then use

Alt+F11
as
before and instead of double clicking Thisworkbook, right

click
in
that
area, choose Insert and then choose Module, then you can paste
your
code
in there. The best way to learn is to record a macro while you
are
doing
a task and then go back and take a look at the generated code,
it's
true
to say that you will see a lot of code that is excess to
requirements
but it will give you a basic idea on how things are

structured.


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' ('The Code Cage' ('The Code Cage'

(http://www.thecodecage.com)))


------------------------------------------------------------------------
Simon Lloyd's Profile:
'The Code Cage Forums - View Profile: Simon Lloyd'
('The Code Cage Forums - View Profile: Simon Lloyd'

(http://www.thecodecage.com/forumz/member.php?userid=1))
View this thread: '\"Marching ants\" around copied cell - The Code
Cage Forums' ('\"Marching ants\" around copied cell - The Code Cage

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



--
Simon Lloyd

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

------------------------------------------------------------------------
Simon Lloyd's Profile:
'The Code Cage Forums - View Profile: Simon Lloyd'

(http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread: '\"Marching ants\" around copied cell - The Code

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



--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=50724



  #22   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1
Default "Marching ants" around copied cell


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 how
about using this (i know we no longer use Auto_Open etc but it serves a
purpose here), all the code below can go in a standard modlue in the
personal.xls, i assume that it will create the new menu item and work as
planned, not tested (im using 2007 now and i don't like it!):

Code:
--------------------
Sub Auto_Close(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("worksheet Menu Bar").Controls("Kill The Ants").Delete
On Error GoTo 0
End Sub

Sub Auto_Open()
With Application
.CommandBars.ActiveMenuBar.Enabled = True
For Each c In .CommandBars("Worksheet menu Bar").Controls
If c.Caption = "Kill The Ants" Then c.Delete
Next c
Set cb = .CommandBars("Worksheet Menu Bar").Controls.Add(Type:=msoControlButton, temporary:=True, ID:=2950, befo=1)
cb.Caption = "Kill The Ants"
cb.TooltipText = "Remove dotted line after paste"
cb.OnAction = ("!KillAnts")
cb.Style = msoButtonCaption
End With
End Sub

Sub KillAnts()
Application.CutCopyMode = False
End Sub

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


T. Valko;186987 Wrote:
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!

--
Biff
Microsoft Excel MVP


"Simon Lloyd" wrote in message
...

I hadn't tried it, i thought that all code stored in the PERSONAL.xls
was available in each workbook you open. This may be crude but how

about
putting that Thisworkbook code in the .xlb file? as this is the base
file that excel references when starting (or at least thats what i
presume) wouldn't every workbook you open already be populated with

that
code?

T. Valko;186952 Wrote:
Doesn't work that way for me. It only works in the Personal.xls

file.

--
Biff
Microsoft Excel MVP


"Simon Lloyd" wrote in message
...

Sorry!, i meant if the code was stored in the ThisWorkbook module

of
PERSONAL.xls would it not then affect every workbook opened?T.
Valko;186343 Wrote:
Would that be the same if the code was saved in PERSONAL.xls?

???

I'm not following you.

--
Biff
Microsoft Excel MVP


"Simon Lloyd" wrote in

message
...

Would that be the same if the code was saved in PERSONAL.xls?T.
Valko;186308 Wrote:
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.

--
Biff
Microsoft Excel MVP


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

Simon Lloyd wrote:
Press and hold Alt & F11, the VBE (visual basic editor)

will
open,
on
the left you will see a list of your worksheets and on

called
ThisWorkbook, they are all code modules, double click the
Thisworkbook
and paste the code i gave.

If you are ever given code for a standard module then use
Alt+F11
as
before and instead of double clicking Thisworkbook, right
click
in
that
area, choose Insert and then choose Module, then you can

paste
your
code
in there. The best way to learn is to record a macro while

you
are
doing
a task and then go back and take a look at the generated

code,
it's
true
to say that you will see a lot of code that is excess to
requirements
but it will give you a basic idea on how things are
structured.


--
Simon Lloyd

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



------------------------------------------------------------------------
Simon Lloyd's Profile:
'The Code Cage Forums - View Profile: Simon Lloyd'
('The Code Cage Forums - View Profile: Simon Lloyd'
('The Code Cage Forums - View Profile: Simon Lloyd'

(http://www.thecodecage.com/forumz/member.php?userid=1)))
View this thread: '\"Marching ants\" around copied cell - The

Code
Cage Forums' ('\"Marching ants\" around copied cell - The Code

Cage
Forums' ('\"Marching ants\" around copied cell - The Code Cage

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



--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' ('The Code Cage' ('The Code Cage'

(http://www.thecodecage.com)))


------------------------------------------------------------------------
Simon Lloyd's Profile:
'The Code Cage Forums - View Profile: Simon Lloyd'
('The Code Cage Forums - View Profile: Simon Lloyd'

(http://www.thecodecage.com/forumz/member.php?userid=1))
View this thread: '\"Marching ants\" around copied cell - The Code
Cage Forums' ('\"Marching ants\" around copied cell - The Code Cage

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



--
Simon Lloyd

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

------------------------------------------------------------------------
Simon Lloyd's Profile:
'The Code Cage Forums - View Profile: Simon Lloyd'

(http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread: '\"Marching ants\" around copied cell - The Code

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



--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=50724

  #23   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default "Marching ants" around copied cell

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.

--
Biff
Microsoft Excel MVP


"Simon Lloyd" wrote in message
...

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 how
about using this (i know we no longer use Auto_Open etc but it serves a
purpose here), all the code below can go in a standard modlue in the
personal.xls, i assume that it will create the new menu item and work as
planned, not tested (im using 2007 now and i don't like it!):

Code:
--------------------
Sub Auto_Close(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("worksheet Menu Bar").Controls("Kill The
Ants").Delete
On Error GoTo 0
End Sub

Sub Auto_Open()
With Application
.CommandBars.ActiveMenuBar.Enabled = True
For Each c In .CommandBars("Worksheet menu Bar").Controls
If c.Caption = "Kill The Ants" Then c.Delete
Next c
Set cb = .CommandBars("Worksheet Menu
Bar").Controls.Add(Type:=msoControlButton, temporary:=True, ID:=2950,
befo=1)
cb.Caption = "Kill The Ants"
cb.TooltipText = "Remove dotted line after paste"
cb.OnAction = ("!KillAnts")
cb.Style = msoButtonCaption
End With
End Sub

Sub KillAnts()
Application.CutCopyMode = False
End Sub

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


T. Valko;186987 Wrote:
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!

--
Biff
Microsoft Excel MVP


"Simon Lloyd" wrote in message
...

I hadn't tried it, i thought that all code stored in the PERSONAL.xls
was available in each workbook you open. This may be crude but how

about
putting that Thisworkbook code in the .xlb file? as this is the base
file that excel references when starting (or at least thats what i
presume) wouldn't every workbook you open already be populated with

that
code?

T. Valko;186952 Wrote:
Doesn't work that way for me. It only works in the Personal.xls

file.

--
Biff
Microsoft Excel MVP


"Simon Lloyd" wrote in message
...

Sorry!, i meant if the code was stored in the ThisWorkbook module

of
PERSONAL.xls would it not then affect every workbook opened?T.
Valko;186343 Wrote:
Would that be the same if the code was saved in PERSONAL.xls?

???

I'm not following you.

--
Biff
Microsoft Excel MVP


"Simon Lloyd" wrote in

message
...

Would that be the same if the code was saved in PERSONAL.xls?T.
Valko;186308 Wrote:
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.

--
Biff
Microsoft Excel MVP


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

Simon Lloyd wrote:
Press and hold Alt & F11, the VBE (visual basic editor)

will
open,
on
the left you will see a list of your worksheets and on

called
ThisWorkbook, they are all code modules, double click the
Thisworkbook
and paste the code i gave.

If you are ever given code for a standard module then use
Alt+F11
as
before and instead of double clicking Thisworkbook, right
click
in
that
area, choose Insert and then choose Module, then you can

paste
your
code
in there. The best way to learn is to record a macro while

you
are
doing
a task and then go back and take a look at the generated

code,
it's
true
to say that you will see a lot of code that is excess to
requirements
but it will give you a basic idea on how things are
structured.


--
Simon Lloyd

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



------------------------------------------------------------------------
Simon Lloyd's Profile:
'The Code Cage Forums - View Profile: Simon Lloyd'
('The Code Cage Forums - View Profile: Simon Lloyd'
('The Code Cage Forums - View Profile: Simon Lloyd'

(http://www.thecodecage.com/forumz/member.php?userid=1)))
View this thread: '\"Marching ants\" around copied cell - The

Code
Cage Forums' ('\"Marching ants\" around copied cell - The Code

Cage
Forums' ('\"Marching ants\" around copied cell - The Code Cage

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



--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' ('The Code Cage' ('The Code Cage'

(http://www.thecodecage.com)))


------------------------------------------------------------------------
Simon Lloyd's Profile:
'The Code Cage Forums - View Profile: Simon Lloyd'
('The Code Cage Forums - View Profile: Simon Lloyd'

(http://www.thecodecage.com/forumz/member.php?userid=1))
View this thread: '\"Marching ants\" around copied cell - The Code
Cage Forums' ('\"Marching ants\" around copied cell - The Code Cage

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



--
Simon Lloyd

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

------------------------------------------------------------------------
Simon Lloyd's Profile:
'The Code Cage Forums - View Profile: Simon Lloyd'

(http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread: '\"Marching ants\" around copied cell - The Code

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



--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=50724



  #24   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 21
Default "Marching ants" around copied cell

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


  #25   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default "Marching ants" around copied cell

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






  #26   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default "Marching ants" around copied cell

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






  #27   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default "Marching ants" around copied cell

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








  #28   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 21
Default "Marching ants" around copied cell

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

RC
  #29   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 21
Default "Marching ants" around copied cell

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 WindowHide

This will hide the file.

Now, save the file:

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

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

  #30   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 15,768
Default "Marching ants" around copied cell

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!

--
Biff
Microsoft Excel MVP


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

This will hide the file.

Now, save the file:

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

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





  #31   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1
Default Ok, I got side tracked yesterday...Here goes...

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


On Thursday, January 15, 2009 10:04 AM Riccol wrote:


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

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info



On Thursday, January 15, 2009 1:38 PM T. Valko wrote:


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.

--
Biff
Microsoft Excel MVP


"Riccol" wrote in message
...



On Thursday, January 15, 2009 1:56 PM Riccol wrote:


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.

Andy Pope wrote:



On Thursday, January 15, 2009 1:59 PM Riccol wrote:


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.

T. Valko wrote:



On Thursday, January 15, 2009 4:15 PM Gord Dibben wrote:


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

Alternative..........

Sub Ant_Killer()
Application.CutCopyMode = False
End Sub


Gord Dibben MS Excel MVP



On Thursday, January 15, 2009 4:29 PM T. Valko wrote:


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?

--
Biff
Microsoft Excel MVP


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...



On Thursday, January 15, 2009 4:43 PM Simon Lloyd wrote:


The simplest way that requires no extra movement is to put this in the
ThsiWorkbook module:
Code:
--------------------
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.CutCopyMode = False
End Sub
--------------------
right after you have pasted it "kills" the ants!Riccol;183577 Wrote:


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=50724



On Friday, January 16, 2009 1:42 AM SulemanPeerzad wrote:


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.
--
Thanks
Suleman Peerzade


"Riccol" wrote:



On Friday, January 16, 2009 7:09 PM Riccol wrote:


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

T. Valko wrote:



On Friday, January 16, 2009 7:34 PM Simon Lloyd wrote:


Press and hold Alt & F11, the VBE (visual basic editor) will open, on
the left you will see a list of your worksheets and on called
ThisWorkbook, they are all code modules, double click the Thisworkbook
and paste the code i gave.

If you are ever given code for a standard module then use Alt+F11 as
before and instead of double clicking Thisworkbook, right click in that
area, choose Insert and then choose Module, then you can paste your code
in there. The best way to learn is to record a macro while you are doing
a task and then go back and take a look at the generated code, it's true
to say that you will see a lot of code that is excess to requirements
but it will give you a basic idea on how things are structured.Riccol;186154 Wrote:


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=50724



On Friday, January 16, 2009 7:49 PM Riccol wrote:


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

Simon Lloyd wrote:



On Friday, January 16, 2009 8:13 PM Gord Dibben wrote:


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

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

On Fri, 16 Jan 2009 19:09:14 -0500, Riccol wrote:



On Friday, January 16, 2009 9:45 PM T. Valko wrote:


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.

--
Biff
Microsoft Excel MVP


"Riccol" wrote in message
...



On Saturday, January 17, 2009 12:02 AM Simon Lloyd wrote:


Would that be the same if the code was saved in PERSONAL.xls?T. Valko;186308 Wrote:


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=50724



On Saturday, January 17, 2009 12:18 AM T. Valko wrote:


???

I am not following you.

--
Biff
Microsoft Excel MVP



On Saturday, January 17, 2009 12:31 AM Simon Lloyd wrote:


Sorry!, i meant if the code was stored in the ThisWorkbook module of
PERSONAL.xls would it not then affect every workbook opened?T. Valko;186343 Wrote:


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=50724



On Saturday, January 17, 2009 5:04 PM T. Valko wrote:


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

--
Biff
Microsoft Excel MVP



On Saturday, January 17, 2009 5:17 PM Simon Lloyd wrote:


I hadn't tried it, i thought that all code stored in the PERSONAL.xls
was available in each workbook you open. This may be crude but how about
putting that Thisworkbook code in the .xlb file? as this is the base
file that excel references when starting (or at least thats what i
presume) wouldn't every workbook you open already be populated with that
code?

T. Valko;186952 Wrote:


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=50724



On Saturday, January 17, 2009 5:49 PM T. Valko wrote:


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!

--
Biff
Microsoft Excel MVP


"Simon Lloyd" wrote in message
...



On Saturday, January 17, 2009 6:33 PM Simon Lloyd wrote:


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 how
about using this (i know we no longer use Auto_Open etc but it serves a
purpose here), all the code below can go in a standard modlue in the
personal.xls, i assume that it will create the new menu item and work as
planned, not tested (im using 2007 now and i don't like it!):

Code:
--------------------
Sub Auto_Close(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("worksheet Menu Bar").Controls("Kill The Ants").Delete
On Error GoTo 0
End Sub

Sub Auto_Open()
With Application
.CommandBars.ActiveMenuBar.Enabled = True
For Each c In .CommandBars("Worksheet menu Bar").Controls
If c.Caption = "Kill The Ants" Then c.Delete
Next c
Set cb = .CommandBars("Worksheet Menu Bar").Controls.Add(Type:=msoControlButton, temporary:=True, ID:=2950, befo=1)
cb.Caption = "Kill The Ants"
cb.TooltipText = "Remove dotted line after paste"
cb.OnAction = ("!KillAnts")
cb.Style = msoButtonCaption
End With
End Sub

Sub KillAnts()
Application.CutCopyMode = False
End Sub

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


T. Valko;186987 Wrote:


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=50724



On Saturday, January 17, 2009 10:15 PM T. Valko wrote:


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.

--
Biff
Microsoft Excel MVP


"Simon Lloyd" wrote in message
...



On Monday, January 19, 2009 9:55 AM Riccol wrote:


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



On Monday, January 19, 2009 2:25 PM T. Valko wrote:


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


--
Biff
Microsoft Excel MVP



On Tuesday, January 20, 2009 10:51 PM T. Valko wrote:


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



On Wednesday, January 21, 2009 2:51 AM T. Valko wrote:


Ooops!

Missed a step:


Should be:

Right click on any toolbar
Select Customize
Select the Commands tabMacros


--
Biff
Microsoft Excel MVP



On Wednesday, January 21, 2009 8:27 AM Riccol wrote:


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

RC



On Thursday, January 22, 2009 12:27 PM Riccol wrote:


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:


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



On Thursday, January 22, 2009 1:48 PM T. Valko wrote:


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!

--
Biff
Microsoft Excel MVP


"Riccol" wrote in message
...




  #32   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 1
Default "Marching ants" around copied cell

On Thursday, 15 January 2009 15:04:33 UTC, Riccol wrote:
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


Simple, just right click the cell with the dancing ants, select format, press okay in the pop-up window and it's done.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
HOW DO I SURROUND DATA WITH COLORFUL MARCHING ANTS novastar102c Excel Discussion (Misc queries) 1 November 10th 08 03:21 PM
At what times do the "moving ants" appear around a cell? sheana New Users to Excel 1 February 6th 08 11:47 AM
Turn off selection marching ants in Excel 2003 [email protected] Excel Discussion (Misc queries) 1 July 10th 07 10:01 PM
How to setup "marching ants" border Excel cell? OK in MS Word. NJDevil Excel Discussion (Misc queries) 3 January 31st 07 06:46 PM
Excel should include flashing cells/block and marching ants like W waholt Excel Worksheet Functions 2 October 21st 05 06:56 PM


All times are GMT +1. The time now is 09:32 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"