Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Disabling macros (xl2000)

    I have an app in which I want to prevent the user from running the macros via TOOLS>>MACROS>>RUN.

    Application.CommandBars("Tools").Controls(12).Dele te

    will delete the Macros menu item from the menu (if it is the 12th item in the menu)

    Application.CommandBars("Tools").Controls.Add Type:=msoControlButton, ID:= _
    186, Before:=12

    will restore it.

    Now, realizing that not every user is going to have the MACRO item in the 12th position on the TOOLS menu, I thought I would combine the lines above to identify the item with the ID and then remove it. ( ie replace the relative reference - 12th position- with an absolute reference - ID=186

    When I combined the 2 lines it failed. How do I do this?

    Barring the ability to remove the MACRO item programatically, is there a way of HIDING THE MACRO NAMES or of diabling them from this Menu item yet keep them enabled for use while running the program.
    I seem to recall seeing a reference to hiding the names, but can't locate it now that I want it. I'd be interested in this, even if I can remove the MACRO menu item temporarily.
    Thanks

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Disabling macros (xl2000)

    Instead of
    Sub Name()
    ..
    end sub
    use:
    Private sub name()
    ...
    end sub
    It will not be displayed

    Regarding your questions on commandbars, the best way might be to NOT "edit" any of the std or custom toolbars. I would just disable ALL of the toolbars (substituting your own) and replace the menubar with one of your own. ONLY add in yours, what the user can use in your app. Then at the end delete your custom ones and re-enable the toolbars. This alleviate the need to check/delete individual items on all the toolbars.

    Steve

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Disabling macros (xl2000)

    Other way

    at top write:
    Option Private Module

  4. #4
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Disabling macros (xl2000)

    Thanks Steve and Servando

    I see the "Private" in either case keeps the macros hidden. However, it also prevents the macros from being called from outside of the module, hence all macros would have to be stored in one module - which is fine for a small project, but a bit cumbersome during development ( to find macros more easily when I have 50-100 macros, I keep them in categorized modules with descriptive module names).
    .....Just a thought, if I call a macro which is beyond the current module with --- Call ModuleName.MacroName --- would that work? I'll have to try. If you know of a way let me know.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Disabling macros (xl2000)

    Private subs are only available in the module even if you explicitly call them.

    Another way to hide them from the tools - macro list is to add a parameter (whether you use it or not). With a parameter the macro can ONLY be called via code so it will NOT be in the list.

    Steve

  6. #6
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Disabling macros (xl2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Paul

    If you will do something like

    Sub MyMacroCodeThatIDontWantToRun(x)

    it will not show up in the macro list as well.

    X can be optional, so you can call it with or without the need to passing X.

    To so that, change the line to

    Sub MyMacroCodeThatIDontWantToRun(Optional x)

    Oh and don't include the ' in Don't, otherwise you will get something like ... <font color=red>Sub MyMacroCodeThatIDon()</font color=red><font color=448800> 'tWantToRun(Optional x)</font color=448800>

    But you knew that <img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/drop.gif border=0 alt=drop width=23 height=23>.

    Also instead of deleting, I would prefer disableing or hiding, this way you don't need to reinstall and figure where to put it back and what have you, simply unhide it, or activate it.

    Hope this helps.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  7. #7
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Disabling macros (xl2000)

    Steve and Wassim

    Yes! This parameter option is just what I needed. Keeps the macros hidden yet I can call them from anywhere. At least that is what I gather from your description - haven't tried it yet.


    Thanks

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Disabling macros (xl2000)

    You can call them from another macro or an event (button click or something). You can NOT assign them to a toolbar button OR to an items in the FORMS toolbar, though you CAN call them from a "click" on items in the controls toolbox

    Steve

Posting Permissions

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