Transfer PERSONAL.xls

[jnix]

I'm developing several workbooks to be used by others. Is there any way I can transfer my PERSONAL.xls to them so they can use common macros or must I put the macros in every wookbook?
Thanks,
jnix

 

[dyarwood]

Hi

If you record your macro within PERSONAL.XLS, all other workbooks can access those macros. Just make sure you save the PERSONAL spreadshett on exit or you will lose all your macros. It will not prompt you to save PERSONAL if you have just written macros.

dyarwood

 

[jnix]

dyarwood:
Does this work if I transfer the wookbooks to another computer?
Thanks,jnix

 

[dyarwood]

No in that case the macros will need to be within one of the workbooks. You can refer to it from any macro using

Application.Run "Workbookname.xls!Macroname"

Hope that helps

dyarwood

 

[dyarwood]

PS the workbook in which the macro you are refering to must be open.

 

[Gavona]

You could export the module from Personal.xls that you want to share (creates a *.bas file).
Send this to your colleagues (or save in a shared network drive)
then suggest that they:
1) ensure that they have a Personal.xls (eg record a simple macro)
2)import your module.

or you could send them a spreadsheet containing a macro that would do the importing for them. I picked up the following from this site the other day - with the idea of adapting it for pretty much the purpose you describe.

Sub CodeImport()

Dim Msg, Style, Title, Help, Ctxt, Response
Msg = "Import into Personal.xls" '& Chr(13) & "'No' imports into current (active) workbook" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "Import Visual Basic Code (Macro)" ' Define title.

Response = Msgbox(Msg, Style, Title)

If Response = vbYes Then ' User chose Yes.
Workbooks("personal.xls&quot.VBProject.VBComponents.Import ("C:\My Documents\VBA\footer.bas&quot
Else ' User chose No.
ActiveWorkbook.VBProject.VBComponents.Import ("C:\My Documents\VBA\footer.bas&quot
End If

End Sub

Feel a bit guilty 'cos I can't recall who posted the above and I realyy ought to give appropriate credit.


Now if you happen to write some VBA that checks for the existence of Personal.xls, creates it if neccessary and invites the user to select which modules he wants to import (ideally trapping multiple imports of the same module asking to overwrite or abort) then that would earn a star or two from me!! I could use it to distribute standard macros (to create standard footers, apply advanced filters, etc.) and, it would allow them to re-import an updated version of the code at some point in the future.

I have to say though that I am uncomfortable with the concept of macros that can add code to another persons workbook without their knowing.

Good luck.
Gavona

 

[Chattin]

Distribute the relevant macros as an add-in perhaps?

Chris ;-)

Visit

http://www.mrexcel.com/board/index.php

for great xl and xl/VBA help