Closing Userform from VBA when it appears

Hello VBA experts! I have made a Word VBA module to save a copy of a file to specific locations and send the file in an email attachement, all based on different user variables. It all works well, except 1 element.

The user works in a document (report that changes during the progress of a project, from a table-based template) which then have to be saved and sent dependent on which division and the progress. The user works in one personal file that they can update.

My macro is initiated by user choice in a User Form. The User Form open on Document_Close(), to avoid people forgetting to send the information off.

The document is then copied using the Sub below:

Please Login or Register  to view this content.

This creates a exact copy of the document that the macro then edits, mostly removing tables, then saving it to a location and sending it to specific people.

I'd then like to close the file automatically, which I do by using SaveDoc.Close SaveChanges:=wdSaveChanges. Here I run into trouble.

The "SaveDoc" copy of my file is saved as .docx, but apparently still contains it's own, copied macro, because the original .dotm file is still open. Closing the copy file means a copy of the original User Form pops up. I'd like to avoid this, as it can confuse the users. How can I avoid this happening? Can I prevent the SaveDoc file from opening User Form, or do something else?

I have difficulties sharing the full code, as it contains a bunch of internal names, projects and addresses. I hope You can help regardless.


Thank You,

GregersDK

Hi,

I don't know your details, but a small change in code after using 'SaveCopyAs' may help:
https://msdn.microsoft.com/en-us/vba...s-method-excel

Lewis

Originally Posted by LJMetzger

Hi,

I don't know your details, but a small change in code after using 'SaveCopyAs' may help:
https://msdn.microsoft.com/en-us/vba...s-method-excel

Lewis

I'm not sure this works Word, seems it only exists for Excel (where you link) and PowerPoint.

My problem is:

• The document is copied with the VBA code embedded, despite being a .docx (your solution potentially could fix this)
• The UserForm OnClose runs in this new document, and I cannot find a method so far to cancel it

Solving either of these should fix the problem.

It seems 'SaveCopyAs' does work in Word (though I haven't found any documentation of this so far), but I cannot seem to define it as a Document (Dim Doc As Document). It makes it difficult to reliably refer to it.

Sorry for the confusion, I didn't realize it was a Word problem. There are many VBA commands that work that most of us do not know about.

Another potential solution for your problem is to have a controlling Word document that contains all the VBA. I use this technique successfully in Excel all the time even with data files that contain Macros. If you still do not have success, try making a small sample file containing no sensitive information that demonstrates the problem.

I have found out what the problem really is:

When making my new document as a copy of the original, using the original document as the template (marked in red below) causes it to also copy the macros over. How do avoid this? The nearest I've come is to instead make a blank document, then transfer all headers/footers and content afterwards, but this is not without problems either. Making a new blank document prevents the macro from being copied though.

Please Login or Register  to view this content.

Anyone have a better solution to this?


GregersDK

See the following link: https://stackoverflow.com/questions/...-saving-macros

The link deletes all the Macros in the destination file. However, in order to do this, in VBA you need to add a reference to the following Library from Word:
Alt F11 > Tools > References > Microsoft Visual Basic For Applications Extensibility 5.3

I solved it!

After macropod's post I realised I may had overcomplicated the issue, so I got a little creative.

Som explanation: The user will use the .docm file with macros to produce, save and send a report multiple times, with changes.
The problem was that the newly made .docx file also contained the macros. The macros where to run when ActiveDocument.OnClose, so it would repeat the macro running the UserForm.
When the .docm is closed, the .docx no longer contains macros.

I realised the defining difference between the documents was the file extension, so I had the initiating macro read it to determine to show UserForm or not. It looks like this:

Please Login or Register  to view this content.

Simply, if the Active Document has the file extension of .docx, never show the UserForm.


Thanks for Your help, I will mark this thread as solved