How to store VBA code separately on shared drive and allow users to use workbook with buttons

[Bacalhau75]

Hi,

I usually don't get stuck since i tend to find answers on MrExcel and/or on Google searches, but I've spent some hours today on this and can't figure it out.

I have a macro workbook that contains multiple modules, macros, hyperlinks and buttons. I want to start splitting my macro workbooks from the code and only provide users with the workbook (design) and link the code or provide it as an ADD-IN. I'm assuming this would also allow to duplicate a workbook 20 times and only link 1 file or provide the ADD-IN to multiple PC's.

I learned about XLA add-ins today and got it working but i can only execute the macros if i add a button to the tool bar. This macro contains 100's of macros and don't want to add 100 buttons.

The goal I’m looking for is to be able to save the file as XLA first, then save it as XLS which should remove the MODULES (if not i'll remove them), but keep the use of hyperlinks (To Macros) and buttons assigned to macros.

What this macro does in a nutshell... 1 sheet provides about 20 macro solutions to scrape data from various TN3270 screens. First there's a RESET button that clears all the data leaving the sheet only with a list of hyperlinks. Each Hyperlink is named to what it will do and each Hyperlink calls a macro that 'builds' the sheet by adding inputs and outputs and creates a button that is assigned to the actual macro that will perform the scrape.

I'd like to know how to save the code elsewhere and be able to edit it as needed and only provide the workbook without any code attached to it.

Thanks in advance.

 

[RickXL]

Hi,

I expect that I do not know all the ways you could achieve the result you are looking for.

However, one way would be to put all the buttons into the Add-In. You can make changes to the Add-Ins Ribbon and when the Add-In is loaded it will provide the user with extra menus with extra controls.

Another way would be to add a Reference to the Add-In after it is loaded. That way, the macros in the Add-In can be called from the ActiveWorkbook as normal.
I think you should be able to programmatically assign the reference if you can put a couple of lines of code into the ThisWorkbook Module of the non-macro workbook. Otherwise the user would have to do it manually. If you want to keep these workbooks macro-free then this might not be an option.

The more I think about it, the more I think using the Add-Ins Ribbon would be the proper answer. It is not for the faint-hearted, though. There is a bit of a learning curve involved.

 

[Bacalhau75]

Hi,

I expect that I do not know all the ways you could achieve the result you are looking for.

However, one way would be to put all the buttons into the Add-In. You can make changes to the Add-Ins Ribbon and when the Add-In is loaded it will provide the user with extra menus with extra controls.

Another way would be to add a Reference to the Add-In after it is loaded. That way, the macros in the Add-In can be called from the ActiveWorkbook as normal.
I think you should be able to programmatically assign the reference if you can put a couple of lines of code into the ThisWorkbook Module of the non-macro workbook. Otherwise the user would have to do it manually. If you want to keep these workbooks macro-free then this might not be an option.

The more I think about it, the more I think using the Add-Ins Ribbon would be the proper answer. It is not for the faint-hearted, though. There is a bit of a learning curve involved.

So i figured out a way to do sort of what i want and i think it's what you meant on your second paragraph. On the workbook that i'll hand out to users, i had to create a sub calling the public macro that appears on the ADD-IN (XLA file). I can see this being cubersome for large macros but not impossible to do. (i tested with a single button macro) and that works. Thinking outlound multiple users should be able to run this, because it seems that each time a workbook is called, the ADDin is loaded creating a temporary VBA file. Therefore this works fine because the code won't be handed in with every workbook.

Now, while customizing the toolbar, i see that under 'ADDIN tab' you can insert a CUSTOM Toolbar, Menu or Toolbar Commands. it provides me a thin drop down to what looks like a place to put other buttons, but i can't seem to figure out how to place buttons on here. I looked around google again and can't pin point it, everything instructs on just how to place a button tying it to a macro.

Also, i guess to edit the XLA file, i just make changes within the VBA project window? if the XLA is being called from a network folder, it will update the 1 file. Therefore if i have 20 workbooks running the same macro (which i do), with the addin feature can update the macro from any location and in the end i'm updating everyone's code? (guessing a reboot of EXCEL is required). This is pretty good deal.

The addin ribbon works for single purpose macros. lately my macros have become more of application with 10 row headers that provide user feedback, formulas, many buttons etc... where buttons within the the toolbar wouldn't work because it means people would have to read and know which to press. now it's more of a pattern game, they remember where the big button is opposed to the small RESET button is .

 

[RickXL]

I have been having a play with this today.
Please don't take this as a definitive "no problems" answer because it is largely untested and certainly untried in a work environment.

However, it seems that if you have a "Referenced Workbook" which contains lots of macros you can link those macros to Form Buttons on the worksheet by just pasting in the macro name. You need to know the name because it does not appear in the list.
To do this I made the workbook with the macros an Add-In but I did not install the Add-in. I just referenced it. You can do this with a macro in the ThisWorkbook Module of the Active Workbook.

So, workbook is opened, Workbook_Open macro runs, sets up the reference then all the macros are available.
Here is my Workbook_Open macro:

Private Sub Workbook_Open()
    On Error Resume Next
    Application.VBE.ActiveVBProject.References.AddFromFile _
                "C:\Users\RickXL\AppData\Roaming\Microsoft\AddIns\AddIn Using Sheets to Store Data.xlam"
    'MsgBox "Reference <addin using="" sheets="" to="" store="" data="">Added"
End Sub

In fact, I seem to have created a .xlsx file with no macros that will run macros in a referenced workbook. Not sure how I did that.
I think the macro assignment must be storing the workbook name but not showing it.

Still, it shows that there is a lot of scope for some experimentation

regards,</addin>

 

[Bacalhau75]

I have been having a play with this today.
Please don't take this as a definitive "no problems" answer because it is largely untested and certainly untried in a work environment.

However, it seems that if you have a "Referenced Workbook" which contains lots of macros you can link those macros to Form Buttons on the worksheet by just pasting in the macro name. You need to know the name because it does not appear in the list.
To do this I made the workbook with the macros an Add-In but I did not install the Add-in. I just referenced it. You can do this with a macro in the ThisWorkbook Module of the Active Workbook.

So, workbook is opened, Workbook_Open macro runs, sets up the reference then all the macros are available.
Here is my Workbook_Open macro:

Private Sub Workbook_Open()
    On Error Resume Next
    Application.VBE.ActiveVBProject.References.AddFromFile _
                "C:\Users\RickXL\AppData\Roaming\Microsoft\AddIns\AddIn Using Sheets to Store Data.xlam"
    'MsgBox "Reference <addin data="" store="" to="" sheets="" using="">Added"
End Sub

In fact, I seem to have created a .xlsx file with no macros that will run macros in a referenced workbook. Not sure how I did that.
I think the macro assignment must be storing the workbook name but not showing it.

Still, it shows that there is a lot of scope for some experimentation

regards,</addin>

I wish I read this at work. I'll give this a try on Monday. it sounds like what I'm looking for.
I'll let you know how it works out.

Thanks!

 

[RickXL]

No problem.

Best of luck