"Marching ants" around copied cell
Riccol
When I copy a cell(s), the copied area is highlighted with "marching
ants". Seems that after pasting, the "marching ants" around the source
area should go away, but it doesn't. The only way I can get rid of the
"marching ants" around the source is to click like a madwoman in a cell
adjacent to the ants. Sometimes when I do that though, the clicking goes
awry and I get stuck in a tangle that is very hard to explain, but the
only way out is to close the workbook without saving and then re-open it.
So my question is, what's the right way to get rid of the ants
highlighting the source area after copying?
(Excel 2002)
RC
Andy Pope
You can press the ESC button.
Cheers
Andy
--
Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Riccol" <ric...@riccol.com> wrote in message
news:eLSIWKyd...@TK2MSFTNGP03.phx.gbl...
T. Valko
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" <ric...@riccol.com> wrote in message
news:eLSIWKyd...@TK2MSFTNGP03.phx.gbl...
Riccol
to click on a cell. I wonder why that little gem isn't in the Help file.
Thanks, I never would have thought to try Escape.
Riccol
mouse rather than having to reach for the Escape key.
Gord Dibben
SendKeys "{ESC}"
End Sub
Alternative..........
Sub Ant_Killer()
Application.CutCopyMode = False
End Sub
Gord Dibben MS Excel MVP
T. Valko
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
news:jj9vm41ps8dv59jq8...@4ax.com...
Simon Lloyd
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
--
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/showthread.php?t=50724
Suleman Peerzade
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
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
Simon Lloyd
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
Riccol
report back after. (Your method sounds especially good as you say it
kills the ants immediately after pasting.)
RC
Gord Dibben
should be copied into a General/Standard module, not event type code which
would go into Thisworkbook or a worksheet module.
If you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started".
http://www.mvps.org/dmcritchie/excel/getstarted.htm
or Ron de De Bruin's site on where to store macros.
http://www.rondebruin.nl/code.htm
In the meantime..........
First...create a backup copy of your original workbook.
To create a General Module, hit ALT + F11 to open the Visual Basic Editor.
Hit CRTL + r to open Project Explorer.
Find your workbook/project and select it.
Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.
Run or edit the macro by going to Tool>Macro>Macros.
You can also assign this macro to a button or a shortcut key combo.
I would recommend not using the SendKeys macro.........SendKeys can be a
little flaky. I've never noticed but better safe than sorry.
Gord Dibben MS Excel MVP
T. Valko
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" <ric...@riccol.com> wrote in message
news:%23Ztw61D...@TK2MSFTNGP03.phx.gbl...
Simon Lloyd
T. Valko
???
I'm not following you.
--
Biff
Microsoft Excel MVP
"Simon Lloyd" <Simon.Llo...@thecodecage.com> wrote in message
news:Simon.Llo...@thecodecage.com...
Simon Lloyd
Sorry!, i meant if the code was stored in the ThisWorkbook module of
> >
> ------------------------------------------------------------------------
> > Simon Lloyd's Profile:
> (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/showthread.php?t=50724)
T. Valko
Simon Lloyd
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?
> >> > '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/showthread.php?t=50724))
T. Valko
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!
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 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, before:=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
--------------------
> >> ('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/showthread.php?t=50724)))
T. Valko
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
<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
>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" <ric...@riccol.com> wrote in message
news:OVnE9Xke...@TK2MSFTNGP02.phx.gbl...
T. Valko
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" <biffi...@comcast.net> wrote in message
news:um379ume...@TK2MSFTNGP03.phx.gbl...
T. Valko
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
"T. Valko" <biffi...@comcast.net> wrote in message
news:OMzDNu3e...@TK2MSFTNGP03.phx.gbl...
Riccol
RC
Riccol
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
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" <ric...@riccol.com> wrote in message
news:%23wDACbL...@TK2MSFTNGP03.phx.gbl...
Robert Oppenheim
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" <ric...@riccol.com> wrote in message
>>> news:eLSIWKyd...@TK2MSFTNGP03.phx.gbl...
>>>> 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
>>>>>>> news:jj9vm41ps8dv59jq8...@4ax.com...
>>>>>>>> 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/showthread.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/showthread.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 Insert>Module. Paste the code in there. Save the
>>>>>>>>>>>>> workbook and hit ALT + Q to return to your workbook.
>>>>>>>>>>>>>
>>>>>>>>>>>>> Run or edit the macro by going to Tool>Macro>Macros.
>>>>>>>>>>>>>
>>>>>>>>>>>>> You can also assign this macro to a button or a shortcut key combo.
>>>>>>>>>>>>>
>>>>>>>>>>>>> I would recommend not using the SendKeys macro.........SendKeys can be a
>>>>>>>>>>>>> little flaky. I've never noticed but better safe than sorry.
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>> Gord Dibben MS Excel MVP
>>>>>>>>>>>>>
>>>>>>>>>>>>> On Fri, 16 Jan 2009 19:09:14 -0500, Riccol <ric...@riccol.com> 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" <ric...@riccol.com> wrote in message
>>>>>>>>>>>>>> news:%23Ztw61D...@TK2MSFTNGP03.phx.gbl...
>>>>>>>>>>>>>>> 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/showthread.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/showthread.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/showthread.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" <Simon.Llo...@thecodecage.com> wrote in message
>>>>>>>>>>>>>>>>>>>> news:Simon.Llo...@thecodecage.com...
>>>>>>>>>>>>>>>>>>>>> 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/showthread.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" <Simon.Llo...@thecodecage.com> wrote in message
>>>>>>>>>>>>>>>>>>>>>> news:Simon.Llo...@thecodecage.com...
>>>>>>>>>>>>>>>>>>>>>>>> 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 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 tab>Macros
>>>>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>>>>> --
>>>>>>>>>>>>>>>>>>>>>>>>>> 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:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 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" <ric...@riccol.com> wrote in message
>>>>>>>>>>>>>>>>>>>>>>>>>>>>> news:%23wDACbL...@TK2MSFTNGP03.phx.gbl...
thomash...@gmail.com
> 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