Results 1 to 6 of 6
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macros and Protected Projects (2000)

    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

  2. #2
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macros and Protected Projects (2000)

    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

  3. #3
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macros and Protected Projects (2000)

    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.

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macros and Protected Projects (2000)

    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

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macros and Protected Projects (2000)

    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

  6. #6
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Macros and Protected Projects (2000)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •