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

    Hiding a macro (2000)

    I have some code that displays a message box, containing certain statistics about spreadsheets. This information is significant to maintenance of the workbook, but of no interest to the customers. I'd like to be able to run this code via a hotkey, but not to have the name of the macro visible to the user; that is, to make it a "hidden feature" of the workbook. Is this possible? If so, how do I arrange it?

    thanks

    Alan

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Hiding a macro (2000)

    Hi Alan,

    In the VBE you could try adding 'private' at the start of the macro's name. That will stop it appearing if the user tries to access it via <Alt-F8> or Tools|Macro|Macros. You'll need to protect your VB project, though, to stop them getting at it via <Alt-F11> or Tools|Macro|Visual Basic Editor.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

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

    Re: Hiding a macro (2000)

    Thanks. I'll give it a try. The VBA code is password protected already. Not sure how to attach a hotkey if the macro can't be "seen" but I'll experiment.

    cheers

    Alan

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Hiding a macro (2000)

    I don't know about that, but I would use an environment variable in the macro like

    if environ("Username")<>AlanMiller then exit sub

    This would effectively stop anyone other than you from using it.
    There are many variations on this idea.
    David Grugeon
    Brisbane Australia

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

    Re: Hiding a macro (2000)

    Thanks David. That's a neat trick, which will be duly added to the grab bag.
    Unfortunately, in this case, I don't know who'll end up doing their maintenance either - may be several staff ???

    cheers

    Alan

  6. #6
    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: Hiding a macro (2000)

    Another solution to add to ones already suggested is one I use a lot. If I have to make some code Public (because it's used in multiple modules) but I don't want the users to be able to see it in the list of macros (from the macro recorder) or stop them from running it just for the heck of it ("gee, I wonder what this does, duh"), I'll add an optional argument to the parameter list.

    This works because only Macros that have arguments (even if they're public macros) don't show in the 'run macro' list. The macro is still available to those who know it's there (or just called from a button). I use this convention:
    <pre>Public Sub NotSeeThisMacro(optional noArg as Boolean = True)
    ' your code here
    End Sub</pre>

    My 'noArg' variables means ' no arguments' but you can choose anything you want. Now this public macro will not show in the 'macro run list'

    The other solution is to force the user to enter a password before the macro is run. I have some code in one project I did that required an 'admin' access password. I had a button that they clicked and they had to enter a password. If successful, it allowed them to view hidden sheets or hidden columns or unprotect certain things or just run special code.

    HTH, Deb <img src=/S/nosleep.gif border=0 alt=nosleep width=27 height=15>

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding a macro (2000)

    Hey, that *is* a good idea!!!

    <off to archive this post>
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Hiding a macro (2000)

    Thanks Deb - very clever. A couple more "killer tips" for the arsenal. This is exactly the reason I want the macro hidden - "gee, I wonder what this does, duh". The weird and wonderful things these users have managed to do with just the standard visible features astounds and amazes! I don't want to toss in any more to mess up on :-).

    cheers

    Alan

Posting Permissions

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