Call Macro In a Different Workbook or Add a Reference in a Macro?

[Ste_Moore01]

Hi,

I'm having a little trouble with a macro I'm writing.

I have a currently got a macro in my PERSONAL.XLS workbook that adds a macro to the Worksheet_SelectionChange of Sheet1 on the Workbook I have open.

The problem is, the macro that is created needs a reference to Microsoft Forms 2.0 object library.

To get around this I decided to amend my macro so that it calls another macro in my PERSONAL.XLS instead because this Workbook already has a reference to Microsoft Forms 2.0 object library.

Unfortunately, I can't figure out how to call macros on different workbooks.

Can anyone tell me how to do this, or alternatively, could someone tell me how to set a reference in a Macro?

It may sound a little confusing so if you need me to explain better let me know.

Thanks!

 

[Demosthenes&Locke]

If you are using excel on your own computer, personal.xls will always be open so you can always reference the macro by name from any other workbook without a problem.

Are you using this workbook on someone elses computer and want to do so without needing to reference the forms library?

 

[Ste_Moore01]

Well what I'm doing is creating a custom menu of macros that I use regularly.

One of these is an auto-copy macro I've created.

This basically, when turned on, copies the contents of every cell you click on, so you don't have to keep pressing copy or pressing ctrl+c. I made this because I sometimes have to do a lot of copying and pasting.

This uses the Worksheet_SelectionChange part of the sheet to work but it needs a reference to the Microsoft Forms 2.0 object library.

This isn't a problem if I manually set up the spreadsheet I'm working on so that it has the reference and the macro.

However, I want to have as little manual interaction as possible.

Currently, my menu calls a macro on the PERSONAL.XLS spreadsheet which would turn on the autocopy by adding a macro in the Worksheet_SelectionChange part of the selected sheet, so that whenever I click on another cell it fires.

Due to this code needing to be added to this area of every sheet I want the autocopy to be on, every sheet I use it on needs to have a reference to Microsoft Forms 2.0 object library, or I need to call another macro on PERSONAL.XLS as this already has the reference.

Unfortunately, I don't know how to call macro's from different workbooks, and I don't know how to add a reference using VB.

Does this clear it up a little better?

I'm not very good at explaining things

 

[Norie]

Why don't you create a template workbook with the references and code?

You could then use that when you want to create the new workbook(s) and you should have all the required references and code.

 

[Ste_Moore01]

I can do that for any spreadsheets that I am making but I get sent a lot of spreadsheets sent to me from other people so I would have to manually add the references and macros in.

 

[Norie]

Why?

Couldn't you use the template to create a new workbook and copy what's been sent into it?

 

[Demosthenes&Locke]

I see what you're trying to do, and found the answer for you here:

http://www.ozgrid.com/forum/showthread.php?t=22483

Quote follows:

lasw10
Established Member


auto loading vb reference libraries

Thought I'd chuck this on as it's something I use regularly and something I see a lot of other people mentioning in posts....

If you use code reliant on non-standard vb libraries then you can use the GUID for that library to reference it on the workbook being opened - then use the name of the library to remove it again when closing the file. Saves going in and ticking the references everytime!!

The below is what I use when working with ADO - I can not take credit for this as I found it on another forum but thought i'd post it up for others to use.

You can add these to your "ThisWorkbook" Sheet ... will install ADO (2.5 or above) and remove it on open and close of the workbook.

[FONT=Verdana][COLOR=#000000]Private Sub Workbook_Open() [/COLOR][/FONT]
 
[FONT=Verdana][COLOR=#000000]   On Error Resume Next [/COLOR][/FONT]
[FONT=Verdana][COLOR=#000000]    'Reference ADO Object Library using Major / Minor GUID[/COLOR][/FONT]
[FONT=Verdana][COLOR=#000000]   Set ID = ThisWorkbook.VBProject.References [/COLOR][/FONT]
[FONT=Verdana][COLOR=#000000]   ID.AddFromGuid "{00000205-0000-0010-8000-00AA006D2EA4}", 2, 5 [/COLOR][/FONT]
 
[FONT=Verdana][COLOR=#000000]End Sub[/COLOR][/FONT]

</PRE>

Private Sub Workbook_BeforeClose(Cancel As Boolean) 
 
     'remove ADO reference
    Dim x As Object 
    n = Application.VBE.ActiveVBProject.References.Count 
 
    Do While Application.VBE.ActiveVBProject.References.Count > 0 And n > 0 
        On Error Resume Next 
        Set x = Application.VBE.ActiveVBProject.References.Item(n) 
        y = x.Name 
        If y = "ADODB" Then 
            Application.VBE.ActiveVBProject.References.Remove x 
        End If 
        n = n - 1 
    Loop 
 
End Sub

</PRE>
If you don't know the GUID then reference it (by ticking it) and then run this to list all the active libraries and their GUIDs - adapted from NateO post on Mr E

Sub Grab_References()
Dim n As Integer
Sheets.Add
ActiveSheet.Name = "GUIDS"
On Error Resume Next
For n = 1 To ActiveWorkbook.VBProject.References.Count
    Cells(n,1) = ActiveWorkbook.VBProject.References.Item(n).Name
    Cells(n,2) = ActiveWorkbook.VBProject.References.Item(n).Description
    Cells(n,3) = ActiveWorkbook.VBProject.References.Item(n).GUID
    Cells(n,4) = ActiveWorkbook.VBProject.References.Item(n).Major
    Cells(n,5) = ActiveWorkbook.VBProject.References.Item(n).Minor
    Cells(n,6) = ActiveWorkbook.VBProject.References.Item(n).fullpath
Next n
End Sub

</PRE>
Hope that proves as useful for others as it has for me.
​

End quote

Please forgive me for ranting.
[rant]
On a side note, this is how questions that have been answered before should be answered. Not, "It's been answered so many times, google it or search the message board". Having a link isn't really enough either because other message boards change and those links end up pointing to nothing.

Having it like this means every way you can ask a question can be googled and the answer provided.
[/rant]