Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, Australia, Australia
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Running macros in other documents (2000)

    I want to distribute a macro for colleagues to use occasionally. I thought the simplest way would be to put it in a document which also contains instructions, they could then press alt-f8 and choose and run the macro. The macro requires that the active document (ie the file that the macro operates on) is active before running the macro.
    However, if I open both the file with the instructions and the file that the macro should run on, click in the second file to select it, then press alt-f8 I don't see the macro on the list, despite the box showing 'all active documents and templates'. Any suggestions?

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Los Angeles Area, California, USA
    Posts
    7,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running macros in other documents (2000)

    Hi Bruce:
    One of the VBA experts could probably explain this better, but when you have macros in Doc1.doc, they only operate when that document is active. If you switch to another document, you won't find the macro. In order to have a macro available to a given document (e.g. Doc2.doc), the macro must either be in Doc2.doc, in the template attached to Doc2.doc, or in a global template. A global template is either one in your startup folder or normal.dot.

    Therefore, if you want your macro to be available for someone else's documents, save your Doc1.doc (the one with the macro) as a template. Send the template to your colleagues with instructions to do one of the following:
    1. Place the template in their Word startup folder, if these macros should be available to ALL their documents.
    2. Place the template in their user or template folder, & load it when needed. They would do this by going to Tools/Options/Templates & Add-ins... & putting a checkmark next to the template (after adding it once). This is useful when the macro is needed for only certain types of documents.
    3. If the macros are needed for a specific document type only, they can place the template in their templates folder & choose File/New to create a document based on that template.

    If you're on a network, you can place the template in the Workgroup templates folder & they can also use File/New...
    Cheers,

  3. #3
    Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, Australia, Australia
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running macros in other documents (2000)

    Thanks for the clear explanation. Is there any way I can avoid my users going through all this? Eg is there a way I can get the macro to shift to their document of choice before running? I suppose it must be possible to 1) present the user with list of open documents and ask them to choose the one they want to work on, and 2) make that document active and run the remainder of the macro.
    Anyone have any code samples that do this?

  4. #4
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running macros in other documents (2000)

    Phil,

    I think you explained it better than I could. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  5. #5
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running macros in other documents (2000)

    Bruce,

    There is usually a way to do stuff in Word.

    To show the user a list of document you will need to use a UserForm.

    I didn't have a sample a few minutes ago, but now I do <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Hopefully this should give you enough to get you going.

    One thing to keep in mind is that the UserForm is modal by default so the user won't see the document shift, but it will "behind-the-scenes". You can set the form to be non-modal, which will allow the user to see the document shift to the one they selected. All you will need to change to get this is to change the ShowModal property of the form to False.

    Ask if you have any questions about the code in the doc.
    Attached Files Attached Files
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Running macros in other documents (2000)

    Bryan,

    This seemed interesting so I downloaded the att and tried it. I got a message saying the macros are not enabled and to see help in the host application. Help was of no help. All I see in the Project window is the Name property. Help!

    Fred

  7. #7
    Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, Australia, Australia
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running macros in other documents (2000)

    Thanks. That's done the trick.

  8. #8
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running macros in other documents (2000)

    Your security settings are set to high, which disables macros. You will need to drop it down to medium.

    From the main Word Window Select Tools|Macro|Security... On the Security Level Tab, make sure the Medium Radio button is selected.
    Click OK, restart Word and reopen the attachment.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Running macros in other documents (2000)

    Bryan,

    Thanks. I've only been running Word 2000 a few months so hadn't run into encounters of this kind.

    But now I'm very curious.

    First, I couldn't see the form when running so I put in a frmOpenDocs.Show statement at the end of the UserForm_Initialize sub. No problem. But I ran into an error 91 (Object variable or With Block variable not set) on Unload Me at the end of the cmdRunCode_Click sub. Tried putting in a frmOpenDocs.Hide although I didn't think it would work and it didn't. Not sure what variable it's talking about that wasn't set but I'm guessing it has something to do with the Show than I added.

    Second, I would want to be able to run the UserForm_Initialize from the Word environment, not from VBE. So I tried putting in a small public sub in the basSAmple module to just call UserForm_Initialize. Of course, I quickly realized that I have no idea of the syntax to go from one module to another and made a guess of frmOpenDocs.UserForm_Initialize. This didn't work at first since UserForm_Initialize is a private sub in the form module. I changed it to public and that worked. I had wanted to keep it private so it wouldn't show up in the menu of macros from Word (Tools | Macro | Macros). But it didn't even if it's public since it's associated with a Form module. But is there any way to access a private sub in 1 module from the other module?

    Thanks for the learning experiences.

    Fred

  10. #10
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running macros in other documents (2000)

    Fred,

    How did you run the form? The easiest way for something like this is to go into the VBE and open the form and then press F5. That will run the form. Normally I put a sub in to run that will open the form, only I forgot this time <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

    The Error 91 is because of the extra line of code you added.

    To open the form from within Word you have to do 2 things.

    1) Create a Public Sub that opens the form

    Sub sOpenForm()
    frmOpenDocs.Show
    End Sub

    2) Add a button to a ButtonBar or a Menu Item that runs the sOpenForm Sub

    That's it.

    The UserForm_initialize sub is an event handler for the Initialize event of the UserForm. It really sholdn't be called directly. You can, but I would recommend it. The initalize even fires automatically when UserForm gets loaded.

    Lastly, there is no way to directly access a private sub in one module from another. Private means that only another procedure in the SAME module can call the Private procedure. To indirectly call it, make a Public procedure in that module that calls the Private procedure. Does that make sense?

    Hopefully all this makes sense, I have been writing this in a disjointed fashion (my 3 & 5 year old are over tired and still up) If not, please ask for clarification.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  11. #11
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Running macros in other documents (2000)

    Bryan,

    Thanks for all the help.

    One of my problems was putting the form.Show in the Initialize sub - not realizing that if I put Show in a general module that it would run the Initialize event. So that solved both problems:
    - putting Show in general module in a public sub made it visible to the Word environment (which I knew how to do)
    - deleting the form.Show in the Initialize and the form.Hide in the Run Command got rid of the error 91.

    Fred

Posting Permissions

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