Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    requiring the macro be run before a file will open (Office 2000/xp)

    Does anyone have any suggestions for requiring a macro be run before a file will open? In other words - if the user doesn't agree to run macros in the file. The file should close.

    Thanks
    TC

  2. #2
    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: requiring the macro be run before a file will open (Office 2000/xp)

    Not really, since disabling the macros would disable your close routine. However, what do you think of this method of controlling access to your document?

    1. <LI>Create a document (Doc1) that provides general information about how it is necessary to Enable macros to access the real document (Doc2).

      <LI>Embed Doc2 in Doc1 as an object (Insert|Object|Create from File...)

      <LI>Protect Doc1 for Forms using a strong password.

      <LI>Create an AutoOpen macro in Doc1 that unprotects Doc1 so the user, if he allows macros to run, can access Doc2.

      Unfortunately, this probably doesn't accomplish everything you're interested in doing, unless you add a step:

      <LI>Have your Doc1 AutoOpen macro launch Doc2 and run whatever macro you want to run in Doc2.
    There was a thread on this board about embedded documents and AutoOpen, and I think we couldn't find a way to make AutoOpen run when an embedded document is launched by double-clicking on it.

    Nothing else leaps to mind...

  3. #3
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    482
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: requiring the macro be run before a file will open (Office 2000/xp)

    I've run into the same issue. I haven't actually done this yet, but I planned a less elegant approach that does not require two documents. This is more along the lines of a strong suggestion rather than "requiring" macros to be enabled.

    It does require a paragraph that you cannot delete at the beginning of your document.

    1. You can create a large text box with a warning about not disabling macros and put it right in the middle of the first page.

    2. You could have an AutoOpen macro that either deletes the text box, or moves this graphic to a negative horizontal or negative vertical position that makes it unviewable.

    3. Then you would need to have an AutoClose macro to recreate the text box, or make it viewable again and to save the document. However, you would probably only want the "Save" to fire if it has been saved during this session. Otherwise, your file dates would change every time you open the document regardless of whether the user actually made any changes..

    If the user disables macros, the big hairy <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23> sign would be right in their face to remind them they really need to close the document and reopen while enabling macros.

    You may want to disable screen refreshing for the macros, especially the AutoClose.

    Just a thought!!
    Troy

  4. #4
    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: requiring the macro be run before a file will open (Office 2000/xp)

    I've done the same thing that jscher2000 suggests in all my models. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> I have code at workbook close that prepares the model (.xls file) for the next time it's open. I assume worse case - macros disabled so I hide (set .Visible=xlsheetveryhidden) all sheets except an "Intro" sheet and a "legal" sheet (contains a legal disclosure for my company).

    The "Intro" sheet has text on it that says this model requires macros to be enabled and it also has any other caveats (like it's only been tested in xl97 and xl2000, etc). If the user opened the xls file w/o macros enabled, all they see is these two sheets. They can't get to any other sheets because they're hidden (super hidden, not just set to hidden).

    At this point the user closes the file and re-opens it with macros enabled. Now the code displays this "Intro" sheet for two seconds and then displays the legal disclosure worksheet (just a text box with all the legal poop in it. This sheetI has [Accept/Reject] buttons that the user must select before continuing. If they select [Reject], the model shuts down, if they select [Accept], the code unhides all the other worksheets and puts the model into my working state ready for use.

    I recently moved this code from workbook_close() event to workbook_save() event to avoid the problem (also stated by jscher2000) where the workbook is always saved even if the user doesn't change any data cells. Either way, it works and is the best you can do since macros need to be enabled before any of your automation code can work.

    Deb <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: requiring the macro be run before a file will open (Office 2000/xp)

    Sounds very reasonable - any suggestions for PPT? [img]/forums/images/smilies/wink.gif[/img]
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  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: requiring the macro be run before a file will open (Office 2000/xp)

    I've never done VBA in PPT but would figure if it supports an Open and Close event that the same concept will work. It's a matter of knowing what slides to hide and when to display them.

    At PPT_Open, display "Intro" slide (and legal disclosure as I did), wait for user to accept disclosure (if applicable) or just pause a few seconds to let them read the text and then unhide the PPT slides and display the first slide.

    At PPT_Close, hide all slides except for the "Intro" slide (do this in case user doesn't turn on macros next time it's opened). Save the PPT file so these changes take affect. Or you can do the work in PPT_Save instead of PPT_Close.

    Deb <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

Posting Permissions

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