Macros and Protected Projects (2000)

**AlanMiller** · 2003-09-26 02:53

I'm not hopeful of an easy/ any solution to this one, but one never knows <img src=/S/grin.gif border=0 alt=grin width=15 height=15>. I maintain a project in XL for a company, which is used by personnel of varying skill levels. The project is password protected so that only experienced users can get into the works. The problem is that users at all levels want to record their own macros for automating their own specific tasks. Is it possible to allow for this, but maintain the protection level of the "coded" portion of the project? I have thought about making the coded procedures "invisible" on the list of macros available from the dropdown Tools menu (easy enough) but this still doesn't prevent everything appearing in full glory if the user decides to edit one of their own visible macros.

Alan

**jujuraf** · 2003-09-26 04:34

No this isn't possible <img src=/S/disappointed.gif border=0 alt=disappointed width=15 height=15>. As you know code which is recorded as well as code written from scratch are all stored in the same place. You can't selectively protect some modules/forms/classes and view others. I'm glad none of my users want this, I'd freak! Luckily my folks don't even know VBA exists they just admire the 'magic' I can create *ha*

If they insist and you have to rely on the honor system, then I'd add serious sounding comments at the top of all of your modules and all subs to scare them off of touching anything. You might always want to rename your modules/forms/classes such that their name is obviously different from anything they might create. Like if you have a module Summary, rename it to ADMIN.Summary. That way when they see the list of modules, it's more obvious which ones are for the project and which ones are for them (the new ones they created).

I'd also make copies of all the code (export all workbook/sheet code, modules, forms, classes) for the time someone messes with your code. It'll then be easier to fix it (or just give them a new copy of the workbook but then it won't have their own macros in it).

You should also make sure that any of your public subs do not appear in the list of macros to be run (from the VB tool bar). At least that way they can't accidently run them. I do this by adding a fake optional argument to each Public sub that I can't make private. Like this:

Public Sub PrintThis(Optional noArgs as Boolean = True)

This stops it from showing in the macro list but it doesn't prevent it from being called as usual w/o arguments.

Deb

**Howard Kaikow** · 2003-09-26 05:17

1. Compile your stuff in a VB DLL and just put stub code in the workbook to call the code in the DLL.

2. Put your code in a workbook in the Excel startup directory.

**AlanMiller** · 2003-09-27 02:04

Thanks for your input Deb. Some of your ideas are appealing and would probably work well, if only the users wouldn't insist of "fiddling". In these cases, nothing short of locking the project seems to work. I use exactly the "trick" you suggest to hide public macros (maybe I got it from you <img src=/S/grin.gif border=0 alt=grin width=15 height=15> ) but I "privatize" most code where possible.

Alan

**AlanMiller** · 2003-09-27 02:46

Hi Howard

These are ideas I'd never thought of. The .dll is particularly appealing, short of the fact that I've never tried it before <img src=/S/grin.gif border=0 alt=grin width=15 height=15> and don't have access to VB proper on my home machine. I presume that one needs full VB to compile a .dll.

The startup directory idea is very appealing though, but also unfamiliar. I'm not sure if I've got it right, but how does this variation sound? The "working" workbook (the one the users want to play in) contains an auto open macro to fire the "code" workbook, which contains the locked project. The "code" workbook also contains the "very hidden" worksheets that hold correctly validated data that users input via their working copy. Just to put you in the picture better, the complexity of the whole setup is such that a lot of "features" have had to be invented to ensure that a valid (hidden) set of data is maintained, but the users are presented with views of this for insert/ modify/ delete operations. These then pass through validation tests before being allowed on the "real" datastore worksheets.

The particular appeal with your suggestion (of two workbooks) is that it appears possible (at last) to genuinely isolate the private stuff, without restricting the user from their familiar Excel flexibility. Even better would be to have the "code" workbook running hidden in the background, if that's at all possible. I can't imagine any fundamental difficulties in changing the sheet referencing within the code to reflect another (working) workbook - both would be workbooks of defined names. I will give this idea a trial run, since it seems like it will do what I've tried to achieve for a long time, using just the one workbook.

I hope I've got the right end of the stick here, but the prospects of this method seem very good. Many thanks. Any more info appreciated too, of course.

Alan

**jscher2000** · 2003-09-29 03:10

Alan, a COM Add-in also is a DLL. Those can be created either with VB or with the Developer Edition of Office 2000/XP.

Thread: Macros and Protected Projects (2000)

Thread Tools