Using a macro enabled workbook across different computers

[Orion19]

Hello All!

I have a macro that sorts a raw data set and adds a bunch of buttons. Those buttons call methods that search for a specific data set, sort data, or build graphs.

It all works great when used on a single computer, but I'm having trouble if the document is emailed. If I run the macro on my computer, then send it to a colleague, they are unable to use the buttons even if they have the same exact macro file open. If the person I send it to attempts to press one of those buttons, Excel attempts to find the ORIGINAL macro file used to process the data set (the one on my computer). All macro files in question have the same exact name.

I know they can search for the method manually but there are dozens of them within the macro and it gets confusing for someone that isn't used to working with macros. Is there a way to program the buttons to be more flexible? The goal is to be able to run the macro on my computer, email the result to a colleague, and allow them to use the buttons to pull data and build graphs that meet their needs.

Thank you in advance!!

 

[bobsan42]

ActiveX buttons or Form Controls?

A way to do it is make the file Macro-enabled (XLSM or XLSB). The buttons must not be linked directly to the external macros, but each button must have it's own procedure (stored in the sheet code module) which then calls the Public Subs or Functions from your other file.
Then (I guess) you will not have missing links problem while the external macro-file is opened.

You may also look at this: https://www.jkp-ads.com/Articles/FixLinks2UDF.asp

 

[Orion19]

Hi bobsan42! Thanks for your response!

I'm using From Controls because all users are on Macs and I'm pretty sure ActiveX isn't supported. The macro creates the buttons and associated subs, so how does one go about coding that so that it's stored in the sheet code module of the processed raw data set? I reviewed the link you sent but couldn't find an analogous reference to my specific situation (still good info and very much appreciated).

The button is created using the following code. When pressed it calls the sub. Do I need to modify the way this button is created? Thank you again!!!!

    ActiveSheet.Buttons.Add(1, 3706, 120, 30).Select    
    Selection.OnAction = "PercentOfOppsDataPull"
    Selection.Name = "PercentOppDataPull"
    ActiveSheet.Shapes("PercentOppDataPull").ScaleWidth 1.0708333333, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.Shapes("PercentOppDataPull").ScaleHeight 0.775, msoFalse, _
        msoScaleFromTopLeft
    Selection.Characters.Text = "Pull Data"
    With Selection.Characters(Start:=1, Length:=9).Font
        .Name = "Calibri"
        .FontStyle = "Regular"
        .Size = 12
    End With

 

[bobsan42]

The code creating the button is not the issue.
The issue is the location of the macro it calls.
Where is the macro PercentOfOppsDataPull stored - in the same file or in an add-in file or probably in your case - in another file containing a bunch of macros. Is the macro a Public Sub?

To sort it out you must not assign the macro name directly to the button .OnAction
You need to create another macro stored in a code module of the same workbook and it will then call the external macro, e.g.:

Sub PercentOppDataPull_click ()
    Call PercentOfOppsDataPull
end sub

In this way you will not get missing link problem because the macro attached to the button is in the same workbook.
Once the macros-containing workbook is opened they will be accessible to VBA independent of the workbook location.
The macro you will call needs to be Public to make life simpler.
You can even get creative and include in the code a procedure to locate the macro file and call the macro with reference to the file, but first focus on fixing the missing links errors.

Disadvantage of this method is that you have to write such a sub for every button you create.
Another way is to make one sub, which the button name and calls the macro with the same name. So create your button with something like:

...
   ActiveSheet.Buttons.Add(1, 3706, 120, 30).Select    
    Selection.OnAction = "[COLOR=#0000ff]CallMacros[/COLOR]"
    Selection.Name = "[COLOR=#ff8c00]PercentOfOppsDataPull[/COLOR]"
    ActiveSheet.Shapes("[COLOR=#ff8c00]PercentOfOppsDataPull[/COLOR]").ScaleWidth 1.0708333333, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.Shapes("[COLOR=#ff8c00]PercentOfOppsDataPull[/COLOR]").ScaleHeight 0.775, msoFalse, _
        msoScaleFromTopLeft
    Selection.Characters.Text = "Pull Data"
    With Selection.Characters(Start:=1, Length:=9).Font
        .Name = "Calibri"
        .FontStyle = "Regular"
        .Size = 12
    End With
...

and save this in a standard code module in the button workbook:

Sub [COLOR=#0000ff]CallMacros[/COLOR]()
    Application.Run [COLOR=#ff8c00]Application.Caller[/COLOR]
End Sub

The name of the button must be the same as the name of the procedure to be called.

 

[Orion19]

Thank you again so much for the information!

This is the process we are using: First, the user downloads a raw data set from a website. Next, the user opens the file containing the macros I've written. The macro file contains three modules. Module1 organizes the data, adds the buttons, and makes everything look pretty. Module2 contains all the subs that the buttons call. Module3 contains code to process a separate file (not relevant here).

Does it make a difference if the code that creates the button and the sub associated with the button are in different modules? Does the sub need to be called from the Application level to avoid referencing a specific file? If I understand your suggestion correctly, the button will call a sub whose only function is to call the desired block of code using "Application.Run". Is that correct?

Thank you again so much! I promise I googled like crazy before responding!

 

[bobsan42]

Yes , you understood correctly - the only important thing is the button has to call a sub which is in the same workbook as the button.