Add In Issue - No worksheets

[atr140]

Excel 2013

I created a workbook with some VBA code that will be used as a template. I did some research and realized that using an addin was the way to go, in case I would need to update the code.

I found some help on creating the addin by Chip Pearson below.

Installing An Add-In

I am loading the addin through a workbook open event.

    Sub InstallAddIn()
    
    ' This is used to install the addin.... only needed once. Update name to install another addinn
        Dim AI As Excel.AddIn
        Set AI = Application.AddIns.Add(Filename:="[URL="file://\\files\trans\_Dept"]\\files\trans\_Dept[/URL] 52\2015 NBIS Monitoring\Templates\Field Files\Single Span Underclear Sheet.xla")
        AI.Installed = True
    End Sub
Sub InstallAddIn2()

' Use this load at start up to load latest version of the code.

        ''''''''''''''''''''''''''''''''''''
        ' Installed = True to load Add In,
        ' Installed = False to unload Add In
        ''''''''''''''''''''''''''''''''''''
        Application.AddIns("Single Span Underclear Sheet").Installed = True  ' or False
    End Sub
Private Sub Workbook_Open()
'At startup, this macro will trigger to update the addin (code from the template)
Call InstallAddIn2

End Sub

When the workbook opens, the addin in is loaded and I have tested that saving something new to the *.xla file works (just added a line 'This is a test' to the top of a module)

However, the worksheets disappear?

If I unload the add-in and install it manually, either through Chip's code or through the addin button it seems to work fine. Any ideas?

Thanks for the help in advance.

 

[Jerry Sullivan]

Hi atr140,

An add-in's worksheets are always hidden when the add-in is actually installed. If installed the add-in's Workbook.IsAddin property will be set to True, which you can check in the properties window of the VB Editor.

If you are still able to see the add-in's worksheets when you thought you had manually installed it, you probably just have the add-in workbook Open, but it's technically not "Installed".

If you are just wanting to read values from the add-in's worksheets, you can still do that when the worksheets are hidden.
If you want to user to see those worksheets and interact with them, you can copy the worksheet from the add-in to the user's workbook.

 

[atr140]

Jerry,

Your suspicions were correct. The addin was not "Installed" with the manual approach as I once thought. I may not fully understand the functionality of the addin.

Here is my intent. I created a template workbook that a user will take and save to their respective project's folder. This workbook is used to store field measurements. The code takes inputs from one sheet and moves that information to some other worksheets. Additionally, through VBA, some tests and simple math is done and that information is output onto those same sheets. The user will then use that same workbook every few months to store new measurements. Through VBA the old information is moved down and the new information comes in above.

My issue is that the workbook is usable, but I am noticing a few bugs that need to be worked out as we are using the workbook. Currently, only a few sheets have been made using the original template. When I notice an issue, I can easily change it in the template file and all the created workbooks, but as more and more workbooks get created, managing all of those workbooks becomes quite daunting. The only changes that would need to be made at this point would be in the VBA code. The worksheets themseleves would stay the same (and would need to stay the same, because the field measurement values are stored in the worksheets themselves).


Ideally, I would like to "wipe" all the code at startup, and "load" the code from the original template file to pick up any changes in the code that I have made.

Please let me know if that is possible, or anyone needs some additional information!

 

[Jerry Sullivan]

The need to update the VBA code for multiple users and workbooks without negatively impacting existing data is a good reason to use an Add-in.

A typical approach would be to have most or all of your code reside in the Add-In. That code can be run from the Add-In and perform actions on the users' data files. When you have updates to your code, the old Add-In is overwritten by the new version, so there's no need for any code to be transferred to the users' data files.

 

[atr140]

Thanks Jerry,

After doing some more digging, I think I understand the topic much better. I think I had an issue with how to call the macro from the add-in.

Application.Run seems to do the trick!

I appreciate the help!