big dreams.. high hopes.. alot of questions..

[dvenn]

i have an idea, and I'm sure i'm not the first to Invision something like this.. but i have a lot of questions in regards to it..

IDEA:

I would like to have an add-in.. that can be distributed to users (a select group), that will give users access to things.. userforms, functions, etc. These would all be accessible from a custom menu

First I would like to have some sort of user level access system built in..
so a user would install the add-in.. and the add-in would check a datasource (Access table, text file, whatever) and check if the username exists (obtained from ENVIRON("UserName"). If it does exist then it grabs the level and sets that level setting to the registry (or something like that) If needed.

Based on the users level (I think something like simple numbers (1, 2, 3, etc for different levels), I would like certain functions to be available to them..

If the username does not exist then add that user to the datasource at the basic level..

QUESTIONS:

how would you create a user authencation system as described, and would it work as described?

can modules be dynamically loaded to an add-in based off criteria such as user level?

Is there an ON LOAD of sorts that would allow an added module or routine to modify the custom menu already available to the user?

would it be possible to update the addin when required (storing the .xla on a network drive is an option for updating , however having the user use the addin from that location is not (network security settings will not allow this)?

I think thats a goos start...

I'll also explain that this is something I would like to see happen however if it never materializes I would not be extremely heartbroken..

I've had some people tell me it's not possible to do with excel and VBA and that it would require outside applications to make it work (native environmental security settings, etc) it was my local IT guy who says it can't be done.. just looking to shove a little "booyah" in his face...

I have learned alot of VBA in the last few months, however my job is more concept than development, and I feel I still to wet behing the ears to get this up and running.

Long post I know.. and there is still more in my head..

Thanks for taking a look..

I look forward to your suggestions as well as possible solutions..

[Bob Phillips]

IDEA:

I would like to have an add-in.. that can be distributed to users (a select group), that will give users access to things.. userforms, functions, etc. These would all be accessible from a custom menu

First I would like to have some sort of user level access system built in..
so a user would install the add-in.. and the add-in would check a datasource (Access table, text file, whatever) and check if the username exists (obtained from ENVIRON("UserName"). If it does exist then it grabs the level and sets that level setting to the registry (or something like that) If needed.

Based on the users level (I think something like simple numbers (1, 2, 3, etc for different levels), I would like certain functions to be available to them..

If the username does not exist then add that user to the datasource at the basic level..

QUESTIONS:

how would you create a user authencation system as described, and would it work as described?

Data tables for storing the access rights is a common, well-tried, and excellent method. Obviously, the database has to be read at add-in install time to retrieve the access rights, and you will need to have some error code in case the values in memory drop for some reason (go and re-retrieve), but whilst it is not trivial, it is all straight-forward. It also gives you the ability to maintian the system data outwith the add-in.

You could also consider the registry. Each user has a registry hive dedicated to them, in HKEY_CURRENT_USER. Using this you can registry keys in that hive, and if they exist, you know the guy is configured, if not, create the. VBA supplies GetSetting, SaveSetting to manipulate the keys.


can modules be dynamically loaded to an add-in based off criteria such as user level?

Is there an ON LOAD of sorts that would allow an added module or routine to modify the custom menu already available to the user?

I wouldn't do that, I would create the add-in with all possible procedures, and then use access right to determine which are available to the user, and use logic to suppress/hide those not available.


would it be possible to update the addin when required (storing the .xla on a network drive is an option for updating , however having the user use the addin from that location is not (network security settings will not allow this)?

If you store it on a network drive, all users can access it, but you will need them to either uninstall the addin, or close Excel for you to be able to update it. Becauise of this, you usually find add-ins updated overnight, unless there is an emergency update. You could get the add-in to update the rights database with who is on, so that if you do need an emergency update, you know who to contact to get out.


I think thats a goos start...

I'll also explain that this is something I would like to see happen however if it never materializes I would not be extremely heartbroken..

I've had some people tell me it's not possible to do with excel and VBA and that it would require outside applications to make it work (native environmental security settings, etc) it was my local IT guy who says it can't be done.. just looking to shove a little "booyah" in his face...

I have learned alot of VBA in the last few months, however my job is more concept than development, and I feel I still to wet behing the ears to get this up and running.

Long post I know.. and there is still more in my head..

Thanks for taking a look..

I look forward to your suggestions as well as possible solutions..

Sorry, but that is all crap. If there is a will, there is a way. I have created many add-in apps of this type. If the IT guys really don't want it to happen, they have the capability of sabotaging it, but with co-operation, it can be done.

If you want to PM me, I would be happy to work with you to get this started.

[Killian]

Hi Daniel,

You've got (IMO) a very accurate response already. I just thought I'd echo the sentiment... there are a range of tried and tested techiques to achieve your goals with Office VBA. Bringing them all together will require some clear thinking at the design stage but there's nothing radical involved that I can think of.

I was tempted to expand on the desricption of your local IT guys opinion but I've been won over by the elegant and dismissivly simplistic, "crap". I've found it safe to change "can't be done" to "can't be bothered" in these conversations to get a more accurate picture.

[dvenn]

just to defend my local IT guy... he is not against it, nor would he interfer with it.. he speciality is system/network maintenance and his expeirence with programming languages and thei abilities is quite limited..

He just doesn't believe it can be done with a language like VBA... but then again until recently he didn't think I could gain Admin rights on our 100% windows network in under 5 minutes.. (he was wrong there too.. )...

in regards to the "can't be bothered" versus "can't be done".. this is just something I thought of (I figured I was not the first), since everyone has MS Office and VBA.. I would be a nice way to actually make all Office apps work together for the user, instead of just having several separate applications.

Thanks for the responses so far.. XLGibbs.. I look forward to further discussion on this.. I have learned alot from your posts...

Now that I know this is possible I would love to explore this for my own personal knowledge..

[debauch]

Please have a look at http://vbaexpress.com/forum/showthread.php?t=7172 reagarding access levels. The guy who helped me do this, wrote the code to perfection. It is an example .zip file , but you could use this as the start of your 'big dream'. You could assign different macros to different users in clumn c, and when it grabs to user level, then you could have it , either install the add-ins , or if they are assigned basic level, just leave it as is. // I am pretty new to this stuff myself, so I am probably not the right guy to be asking.

Your idea does not seem too far fetched though.

[dvenn]

The only problem I see with that from my idea is that everything thing (as it relates to the users' access is stored in the excel file.. which could potentially be seen by any user, using th file.

I have a partial solution now...

once I have it a little bit more polished I will submit it here to have everyone look at and the tweak to perfection.

Thanks for the reference though..

[XLGibbs]

Thanks for the responses so far.. XLGibbs.. I look forward to further discussion on this.. I have learned alot from your posts...

..

Thanks!

One thing that can really make a difference with a plan like this is the plan itself.

Before designing actual workbooks, modules and coded solutions, it is important to have a pretty clear outline/flow chart of what needs to happen, and a fairly precise, but modifiable decision tree for the necessary workings of your project.

Echo of the above, there is almost always a way to do it, and I have seen many "impossibles" be quicly dismissed.

When testing, work with the basic structure first, identifying steps in the process that can simply trigger msgbox's to test the actual decision tree to make sure that the structure works properly. Small data sets with the same structure can be used to simplify the operation, then plugging in the "production" level information is easy..

One of the IT guys at work asked me what VBA was...after smothering an out loud laugh, i explained in small words for him that it was simply VB using MS Office application's as the container rather than a window's application.