A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

"Marching ants" around copied cell



 
 
Thread Tools Display Modes
  #21  
Old January 18th, 2009, 12:33 AM posted to microsoft.public.excel.newusers
Simon Lloyd[_86_]
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

  #22  
Old January 18th, 2009, 04:15 AM posted to microsoft.public.excel.newusers
T. Valko
external usenet poster
 
Posts: 15,759
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



  #23  
Old January 19th, 2009, 03:55 PM posted to microsoft.public.excel.newusers
Riccol
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


  #24  
Old January 19th, 2009, 08:25 PM posted to microsoft.public.excel.newusers
T. Valko
external usenet poster
 
Posts: 15,759
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




  #25  
Old January 21st, 2009, 04:51 AM posted to microsoft.public.excel.newusers
T. Valko
external usenet poster
 
Posts: 15,759
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






  #26  
Old January 21st, 2009, 08:51 AM posted to microsoft.public.excel.newusers
T. Valko
external usenet poster
 
Posts: 15,759
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








  #27  
Old January 21st, 2009, 02:27 PM posted to microsoft.public.excel.newusers
Riccol
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
  #28  
Old January 22nd, 2009, 06:27 PM posted to microsoft.public.excel.newusers
Riccol
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".

  #29  
Old January 22nd, 2009, 07:48 PM posted to microsoft.public.excel.newusers
T. Valko
external usenet poster
 
Posts: 15,759
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".



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:51 PM.


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