Add-in

First some background. I have a workbook that contains a userform and some ribbon code and general macros that control what the user can do in the workbook.
(For example, I only allow them to select a row on a specific sheet (sheet1), never a particular cell, and if they want to update the row, they have to go via the userform).
A ribbon module then allows them to create a CSV file on a specific network drive that is then picked up automatically, sent to the mainframe and updates various
parameters in our production environment.

Originally, this workbook was only used by one department in Sweden, but now it turns out that a similar department in Denmark is going to use it as well.
The information available on sheet1 is not the same for Denmark and Sweden. At the moment, my solution is to create 2 workbooks, one for each country. Obviously,
the problem then arises if I have to update the macros behind the scenes in any way. I would need access to both workbooks and update the VBA code in both of them.

I could theoretically create one workbook with 2 sheets, one for Denmark and one for Sweden (the "wrong" one being hidden depending on the user).
The trouble with this (as I see it) is that a Dane could activate the Swedish sheet, screw up the data and then send it to production (not what we want).
Again, I could have a list of userids indicating that these people can only see the Danish spreadsheet, and others can only see the Swedish one.
Trouble with this is that it requires manual maintenance of the access lists.

My thought therefore (and I've never done this) would be to create an xla (?) add-in that contains the actual ribbon & VBA code, userform etc, and the workbooks themselves
only contain sheet1.

Is this a sensible way to go, and if so, could someone point me to a good site where I can read up on it (so far, all I've seen are references to Analysis Toolpak and Solver
and examples with functions being used in the add-in).

Thank you.

An addin is always a good way to go, you could have both templates in the addin and copy the appropriate one. But I cannot see that solves your problem, the difference between Sweden and Denmark. You need something to test to let you know which country is using it, and as you say, userids requires central maintenance. Do they have a different Excel language in Sweden and Denmark (I know they can, but do they actually, or do they have a corporate standard)? If they so, you could test for that and pick up the appropriate template.

My idea was to create 2 workbooks; one for Sweden with their specific values on sheet1 and one for Denmark with their values instead. Then, when they open the workbook, I would "pull in" (if that's the correct terminology) the add-in with the various modules, ribbon modules, userform etc etc. That way, all I have to maintain is the actual add-in.

Does that sound sensible ?

You seem to have the logic backwards to me. The addin should be installed all of the time, and you give them buttons to do what they want to do. When they select open a workbook, you give them a file dialog to go select the file, if they want a new one, you then pull out the appropriate template sheet from your addin - that is when you need to know who is using the addin.

Thanks Bob.

It might well be that I have it reversed, but as I said, I've never used an add-in before.

In my naivety, I assumed an add-in was something that was "loaded" when the workbook was opened. Is that wrong ?

My interpretation of how it "should" work (ie, what seems logical to me) is the following.
I distribute 2 workbooks with different contents on sheet 1. One WB is the Swedish one, the other is the Danish one. The ONLY data they contain is what's on
sheet 1 (and possibly, a hidden "technical sheet").
As soon as they activate the workbook, the add-in is loaded and this contains the ribbon modules, userform and the macros needed to run everything.

Have I totally misunderstood what an add-in is (used for)

You could do it that way, I wouldn't but that doesn't mean it is wrong.

Normally, an addin is loaded when the application, Excel, starts. Addins control what happens within their bailiwick, and would usually manage workbooks without any code (the purpose of the addin is to hold the code). They would be available whilst excel is running, ever-ready to do their stuff.

Your way, you would have to have code in both of the workbooks. It wouldn't be much, but it would be replicated. And you still have the issue that the Danes could open the Swedish workbook and mess it up.

Okay

The Danes opening the Swedes (and vice versa) isn't a problem since each WB is on completely different network address that is inaccessible for the other.

My thought (still) is along the lines of distributing the differing WB's to each country. These WB's ONLY contain sheet1. Along with this, I also send them the add-in with the VBA code, userform etc. That way, any updates on the VBA code only require me to send a new version of the add-in (or is there a much simpler method I'm not aware of).

I suppose, in reality, what I'm asking is the following. How would you distribute a source xlsm to multiple customers where you only want to update the VBA code and not the sheet contents ?

I am not sure I understand that last question, what difficulties are you seeing?

Not difficulties, rather, my assumption has been all along that an add-in is the way to go. Maybe I'm simply wrong there.

Let me rephrase the whole thing. Assume YOU, Bob, have sent an xlsm file to 5 customers all over America. Once a month, you send them a data file that can be used in conjunction with the xlsm file. Now you've either discovered a bug in the VBA code or an enhancement you want to introduce. Is there a way of sending ONLY the VBA code rather than a replacement xlsm file ? (I'm assuming the customers' Excel expertise is a 1 on a scale to 5 and therefore am precluding asking the customers to open the VBA editor and import the new code)

Okay. Having worked on this for some time (and my first attempt), I thought I'd try and document what happened and my experiences. Like I've said (?) before, I'm self-taught in VBA coding, so various comments in the Word document might be incorrect, but that will be based on the fact that I never managed to get what I was after to work.

Anyway, FWIW (just discovered that I need to split my word document into 2 parts to be able to upload it)

Here's the second part (had to zip this)