How can I write a macro so that it is available for use for all excel
workbooks that I would open in my Excel 2007. Currently I wrote a macro and
it is working only for the workbook I wrote it under.

Store them in personal.xls

See http://personal-computer-tutor.com/personalxls.htm

"Tigerxxx" wrote:

How can I write a macro so that it is available for use for all excel
workbooks that I would open in my Excel 2007. Currently I wrote a macro and
it is working only for the workbook I wrote it under.

i think i might be trying to do a similar task. this is what i tried, if
it doesnt help, please disregard .




--
infomercialscams

An Excel addin is another way. Whichever way you do this you must make
sure that your code references the correct workbook.


--
royUK

Hope that helps, RoyUK
For tips & examples visit my 'web site' (http://www.excel-it.com/)
------------------------------------------------------------------------
royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=95209

Hi,

How can I reference my code so that it points to "All" workbooks that I
would open in Excel?

"royUK" wrote:


An Excel addin is another way. Whichever way you do this you must make
sure that your code references the correct workbook.


--
royUK

Hope that helps, RoyUK
For tips & examples visit my 'web site' (http://www.excel-it.com/)
------------------------------------------------------------------------
royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=95209

When I have generic macros that I want available whenever I open excel, I'll
write the code to use the activesheet--or even use the current selection.

And those kinds of generic macros would go in my personal.xls/.xla workbook.

If I need something that only runs against certain workbooks (or even certain
types of workbooks), I won't put those macros in my personal.xl* workbook. I'll
create a specific workbook/addin for just those utilities.

Those types of macros may need a second sheet--or look for sheets with specific
names (or range names).

Tigerxxx wrote:

Hi,

How can I reference my code so that it points to "All" workbooks that I
would open in Excel?

"royUK" wrote:


An Excel addin is another way. Whichever way you do this you must make
sure that your code references the correct workbook.


--
royUK

Hope that helps, RoyUK
For tips & examples visit my 'web site' (http://www.excel-it.com/)
------------------------------------------------------------------------
royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=95209



--

Dave Peterson

A couple of examples...

Option Explicit
Sub GenericMacro1()
dim myRng as range
dim myCell as range
dim wks as worksheet

set wks = activesheet

with wks
set myrng = .range("a1",.cells(.rows.count,"A").end(xlup).row)

for each mycell in myrng.cells
if mycell.hasformula = false then
mycell.value = lcase(mycell.value)
end if
next mycell
end with
end sub

or using the current selection

Option Explicit
Sub GenericMacro1()
dim myRng as range
dim myCell as range

set myrng = Selection

for each mycell in myrng.cells
if mycell.hasformula = false then
mycell.value = lcase(mycell.value)
end if
next mycell

end sub

Dave Peterson wrote:

When I have generic macros that I want available whenever I open excel, I'll
write the code to use the activesheet--or even use the current selection.

And those kinds of generic macros would go in my personal.xls/.xla workbook.

If I need something that only runs against certain workbooks (or even certain
types of workbooks), I won't put those macros in my personal.xl* workbook. I'll
create a specific workbook/addin for just those utilities.

Those types of macros may need a second sheet--or look for sheets with specific
names (or range names).

Tigerxxx wrote:

Hi,

How can I reference my code so that it points to "All" workbooks that I
would open in Excel?

"royUK" wrote:


An Excel addin is another way. Whichever way you do this you must make
sure that your code references the correct workbook.


--
royUK

Hope that helps, RoyUK
For tips & examples visit my 'web site' (http://www.excel-it.com/)
------------------------------------------------------------------------
royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=95209



--

Dave Peterson

--

Dave Peterson

Hi, Dave - your answers are really great and I'm learning a lot from them.
I had a workbook with macros to which I was always navigating.
I did Save As: Type: xla: "[Bod Macros].xla" and XL took me to the Add-Ins
folder, so I saved it there.
How do I now make myself a wee button to be able to choose which macro from
that book I want to run (maybe I should be using a xlb or an xls).

I don't quite get the which I would want to use out of xls, xla and xlb.
When I just saved my macros in a xls file in XLStart, that book opened up
every time I ran XL, but I don't want that as I don't ALWAYS use the macros -
I just want them to always be available (unless if I don't have that option).

I keep my addin workbook in XLStart, but I could use the Addins folder and use
Tools|Addins (xl2003 menus) to install that addin.

It's never bothered me that I only use a couple of macros that are in that
workbook--even though the entire workbook with all its macros are opened each
time I open excel.

There are ways of loading an addin on demand, but I don't see the point in most
cases--do you have lots and lots and lots of sub's to worry about?

Saved from a previous post:

Your life will become much simpler if you include code to create the toolbar
when the workbook is opened and include code to destroy the toolbar when the
workbook is closed.

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

In xl2007, those toolbars and menu modifications will show up under the addins.

Bod wrote:

Hi, Dave - your answers are really great and I'm learning a lot from them.
I had a workbook with macros to which I was always navigating.
I did Save As: Type: xla: "[Bod Macros].xla" and XL took me to the Add-Ins
folder, so I saved it there.
How do I now make myself a wee button to be able to choose which macro from
that book I want to run (maybe I should be using a xlb or an xls).

I don't quite get the which I would want to use out of xls, xla and xlb.
When I just saved my macros in a xls file in XLStart, that book opened up
every time I ran XL, but I don't want that as I don't ALWAYS use the macros -
I just want them to always be available (unless if I don't have that option).

--

Dave Peterson

Thanks. I'll look at the code to add and remove the toolbars.
I have maybe a dozen subs in a few modules.

Most of the time I don't need them - its just to make them easily
available, so I'm not keen on the book opening every time I run XL.
I wouldn't mind pressing Alt+F8 to show the pick list.
Tools Add-Ins show the book as ticked, and nothing is disabled, but how do I
then get at them?
Maybe What I need is a single button to open the book when I want hem to
become available. (I might be thinking wrong because I have my head stuck in
the ways Word and PowerPoint work, which are different from each other and
from Excel).

"Dave Peterson" wrote:

I keep my addin workbook in XLStart, but I could use the Addins folder and use
Tools|Addins (xl2003 menus) to install that addin.

It's never bothered me that I only use a couple of macros that are in that
workbook--even though the entire workbook with all its macros are opened each
time I open excel.

There are ways of loading an addin on demand, but I don't see the point in most
cases--do you have lots and lots and lots of sub's to worry about?

Saved from a previous post:

Your life will become much simpler if you include code to create the toolbar
when the workbook is opened and include code to destroy the toolbar when the
workbook is closed.

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

In xl2007, those toolbars and menu modifications will show up under the addins.

Bod wrote:

Hi, Dave - your answers are really great and I'm learning a lot from them.
I had a workbook with macros to which I was always navigating.
I did Save As: Type: xla: "[Bod Macros].xla" and XL took me to the Add-Ins
folder, so I saved it there.
How do I now make myself a wee button to be able to choose which macro from
that book I want to run (maybe I should be using a xlb or an xls).

I don't quite get the which I would want to use out of xls, xla and xlb.
When I just saved my macros in a xls file in XLStart, that book opened up
every time I ran XL, but I don't want that as I don't ALWAYS use the macros -
I just want them to always be available (unless if I don't have that option).

--

Dave Peterson