is there anyway to make a macro that applies to all of excel..
basically to every workbook that i open? like can i make a
permeniant button in excel.. that will be there for every spreadsheet
that i open?

Lots of people keep a workbook named personal.xls in their XLStart folder.

They keep all their macros that they want available whenever excel opens in that
file.

To give myself access to the macros (instead of using tools|macro|macros
dialog), I use one of these.

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)

=====
Ps. If you're going to share this file with others, then don't call it
personal.xls. The recipients could already have that file name in use.

Call it something like:
MithuUtils.xls

mithu wrote:

is there anyway to make a macro that applies to all of excel..
basically to every workbook that i open? like can i make a
permeniant button in excel.. that will be there for every spreadsheet
that i open?

--

Dave Peterson

If you save your macro in your personal.xls file, it will be available to be
run in all workbooks.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"mithu" wrote in message
ups.com...
is there anyway to make a macro that applies to all of excel..
basically to every workbook that i open? like can i make a
permeniant button in excel.. that will be there for every spreadsheet
that i open?

ok so here is something interesting i found.

i dont if this is with every version of excel.. but if i put a macro
in personal.xls
lets say i put

sub emailfile()
....
end sub

that works fine.

but if i put a function that does not work.
for instance i put

Function NumberIt(CompanyCell As Range) As Double
C = CompanyCell.Value
For x = 1 To Len(C)
If IsNumeric(Mid(C, x, 1)) Then n = n & Mid(C, x, 1)
Next x
NumberIt = --n
End Function

i use this to extract a number from a cell from vlookup
so i would use this like =vlookup(numberIt(a1).......
this doesnt work if i put it in personal.. but it does if i put it in
the macro sheet of the book..

really wired.. you guys know anyway to get functions to work on every
workbook?

On Mar 20, 11:22 am, "Bob Flanagan" wrote:
If you save your macro in your personal.xls file, it will be available to be
run in all workbooks.

Bob Flanagan
Macro Systemshttp://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"mithu" wrote in message

ups.com...



is there anyway to make a macro that applies to all of excel..
basically to every workbook that i open? like can i make a
permeniant button in excel.. that will be there for every spreadsheet
that i open?- Hide quoted text -

- Show quoted text -

never mind i get it you have to call it from the personal.xls file

so i would have to put

=vlookup(personal.xls!numberit(a1)....


thanks guys

On Mar 20, 2:09 pm, "mithu" wrote:
ok so here is something interesting i found.

i dont if this is with every version of excel.. but if i put a macro
in personal.xls
lets say i put

sub emailfile()
...
end sub

that works fine.

but if i put a function that does not work.
for instance i put

Function NumberIt(CompanyCell As Range) As Double
C = CompanyCell.Value
For x = 1 To Len(C)
If IsNumeric(Mid(C, x, 1)) Then n = n & Mid(C, x, 1)
Next x
NumberIt = --n
End Function

i use this to extract a number from a cell from vlookup
so i would use this like =vlookup(numberIt(a1).......
this doesnt work if i put it in personal.. but it does if i put it in
the macro sheet of the book..

really wired.. you guys know anyway to get functions to work on every
workbook?

On Mar 20, 11:22 am, "Bob Flanagan" wrote:



If you save your macro in your personal.xls file, it will be available to be
run in all workbooks.

Bob Flanagan
Macro Systemshttp://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"mithu" wrote in message

oups.com...

is there anyway to make a macro that applies to all of excel..
basically to every workbook that i open? like can i make a
permeniant button in excel.. that will be there for every spreadsheet
that i open?- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

That's the main reason I put my macros and functions in an Add-in.

You don't have to preface with the filename.

Save a copy of Personal.xls as File TypeMS Excel Add-in(*.xla)

Stick it in your Office\Library folder and Load it through ToolsAdd-ins.

Leave it checked and it is always available.

Remove Personal.xls from your XLSTART folder.

One note: the Macros don't show up in ToolsMacroMacros.


Gord Dibben MS Excel MVP

On 20 Mar 2007 11:19:59 -0700, "mithu" wrote:

never mind i get it you have to call it from the personal.xls file

so i would have to put

=vlookup(personal.xls!numberit(a1)....


thanks guys

On Mar 20, 2:09 pm, "mithu" wrote:
ok so here is something interesting i found.

i dont if this is with every version of excel.. but if i put a macro
in personal.xls
lets say i put

sub emailfile()
...
end sub

that works fine.

but if i put a function that does not work.
for instance i put

Function NumberIt(CompanyCell As Range) As Double
C = CompanyCell.Value
For x = 1 To Len(C)
If IsNumeric(Mid(C, x, 1)) Then n = n & Mid(C, x, 1)
Next x
NumberIt = --n
End Function

i use this to extract a number from a cell from vlookup
so i would use this like =vlookup(numberIt(a1).......
this doesnt work if i put it in personal.. but it does if i put it in
the macro sheet of the book..

really wired.. you guys know anyway to get functions to work on every
workbook?

On Mar 20, 11:22 am, "Bob Flanagan" wrote:



If you save your macro in your personal.xls file, it will be available to be
run in all workbooks.

Bob Flanagan
Macro Systemshttp://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"mithu" wrote in message

oups.com...

is there anyway to make a macro that applies to all of excel..
basically to every workbook that i open? like can i make a
permeniant button in excel.. that will be there for every spreadsheet
that i open?- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Thats a great idea Gord,

Thanks for the tip

On Mar 20, 3:18 pm, Gord Dibben <gorddibbATshawDOTca wrote:
That's the main reason I put my macros and functions in an Add-in.

You don't have to preface with the filename.

Save a copy of Personal.xls as File TypeMS Excel Add-in(*.xla)

Stick it in your Office\Library folder and Load it through ToolsAdd-ins.

Leave it checked and it is always available.

Remove Personal.xls from your XLSTART folder.

One note: the Macros don't show up in ToolsMacroMacros.

Gord Dibben MS Excel MVP

On 20 Mar 2007 11:19:59 -0700, "mithu" wrote:



never mind i get it you have to call it from the personal.xls file

so i would have to put

=vlookup(personal.xls!numberit(a1)....

thanks guys

On Mar 20, 2:09 pm, "mithu" wrote:
ok so here is something interesting i found.

i dont if this is with every version of excel.. but if i put a macro
in personal.xls
lets say i put

sub emailfile()
...
end sub

that works fine.

but if i put a function that does not work.
for instance i put

Function NumberIt(CompanyCell As Range) As Double
C = CompanyCell.Value
For x = 1 To Len(C)
If IsNumeric(Mid(C, x, 1)) Then n = n & Mid(C, x, 1)
Next x
NumberIt = --n
End Function

i use this to extract a number from a cell from vlookup
so i would use this like =vlookup(numberIt(a1).......
this doesnt work if i put it in personal.. but it does if i put it in
the macro sheet of the book..

really wired.. you guys know anyway to get functions to work on every
workbook?

On Mar 20, 11:22 am, "Bob Flanagan" wrote:

If you save your macro in your personal.xls file, it will be available to be
run in all workbooks.

Bob Flanagan
Macro Systemshttp://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"mithu" wrote in message

oups.com...

is there anyway to make a macro that applies to all of excel..
basically to every workbook that i open? like can i make a
permeniant button in excel.. that will be there for every spreadsheet
that i open?- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -