misi01
New member
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.
(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.