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