Solved

Creating an AutoExec() macro from existing macro

Posted on 2009-04-05
7
606 Views
Last Modified: 2013-12-26
HI,

I have an existing macro and little VB experiance - What i am trying to achieve is the following - The macro  run's whenever the save button is clicked on a Word document - this macro opens a dialog box and forces the user to select one of two buttons (close button has been disabled, the 2 buttons add a property to the document when clicked)
What I now want to do is store this macro as an add-in, maybe if needed renaming to AutoExec()? as I want this macro to run everytime the save button is clicked regardless of the document.

my code is in a number of places and I do not know how to rename my macro, or how to combine this code into a new macro - I would rather it ran as an add-in rather than a macro stored in normal.dot
Please remeber that my VB experiance is limited so please use examples if possible
4 Parts to the macro:

"ThisDocument" code:

Private Sub Document_New()

Call Register_Event_Handler

End Sub
 

Private Sub Document_Open()

Call Register_Event_Handler

End Sub
 

"UserForm1" code:

(disable close button)

Private Declare Function FindWindow Lib "user32" _

  Alias "FindWindowA" (ByVal lpClassName As String, _

  ByVal lpWindowName As String) As Long

 

Private Declare Function GetWindowLong Lib "user32" _

  Alias "GetWindowLongA" (ByVal hWnd As Long, _

  ByVal nIndex As Long) As Long

 

Private Declare Function SetWindowLong Lib "user32" _

  Alias "SetWindowLongA" (ByVal hWnd As Long, _

  ByVal nIndex As Long, ByVal dwNewLong As Long) As Long

 

Const GWL_STYLE = (-16)

 Const WS_SYSMENU = &H80000
 

(button click code to add the property)

Private Sub nonsecureButton_Click()

Call WriteProp(sPropName:="Category", sValue:="Non-Secure")

Unload UserForm1

End Sub
 

Private Sub SecureButton_Click()

Call WriteProp(sPropName:="Category", sValue:="Secure")

Unload UserForm1

End Sub
 

Public Sub WriteProp(sPropName As String, sValue As String, Optional lType As Long = msoPropertyTypeString)

Dim bCustom As Boolean
 

  On Error GoTo ErrHandlerWriteProp

  ActiveDocument.BuiltInDocumentProperties(sPropName).Value = sValue

Exit Sub
 

Proceed:

  bCustom = True
 

Custom:

  ActiveDocument.CustomDocumentProperties(sPropName).Value = sValue

  Exit Sub
 

AddProp:

  On Error Resume Next

  ActiveDocument.CustomDocumentProperties.Add Name:=sPropName, LinkToContent:=False, Type:=lType, Value:=sValue
 

   If Err Then

    Debug.Print "The Property " & Chr(34) & sPropName & Chr(34) & " couldn't be written, because " & Chr(34) & sValue & Chr(34) & " is not a valid value for the property type"

  End If
 

  Exit Sub
 

ErrHandlerWriteProp:

  Select Case Err

    Case Else

   Err.Clear

     If Not bCustom Then

         Resume Proceed

   Else

     Resume AddProp

   End If

  End Select

End Sub
 

Private Sub UserForm_Initialize()

'this hides the X on the caption line

 Dim hWnd As Long, a As Long

 

Dim V As Integer

 

V = CInt(Left(Application.Version, _

InStr(Application.Version, ".")))

 

'If V = 8 this is Word 97

 

If V = 8 Then

 

hWnd = FindWindow("ThunderXFrame", Me.Caption)

 

Else

 

hWnd = FindWindow("ThunderDFrame", Me.Caption)

 

End If

 

a = GetWindowLong(hWnd, GWL_STYLE)

 

SetWindowLong hWnd, GWL_STYLE, a And Not WS_SYSMENU
 

End Sub
 

"Module1" code:

Dim X As New Class1

Public Sub Register_Event_Handler()

    Set X.App = Word.Application

End Sub
 

"Class 1" code:

Public WithEvents App As Word.Application
 

Private Sub App_DocumentBeforeSave(ByVal Doc As Document, SaveAsUI As Boolean, Cancel As Boolean)

UserForm1.Show
 

End Sub

Open in new window

0
Comment
Question by:sbsbats
  • 4
  • 3
7 Comments
 
LVL 4

Accepted Solution

by:
Xcone earned 500 total points
Comment Utility
The easiest way the create an addin, it to create your macro code, and save the document as a template (SaveAs -> Template (*.dot)). Save your template in the Word Startup Folder. To find out which folder is the startup folder goto Tools > Options > FileLocations > Startup. All *.dot files will automaticly load when Word starts up (hence the name Startup).

Now, to overload the the save action, create a module in in your addin, and add the macro "FileSave" and/or "FileSaveAs" in it. This will prevent Word's default save routine from running, and will execute your own macro's instead.

All you have to do now, is call your own save routines from these overloads.
Sub FileSave()

  // Your own save routine

End Sub
 

Sb FileSaveAs()

  // Your own save as routine

End Sub

Open in new window

0
 

Author Comment

by:sbsbats
Comment Utility
Cheers for the reply but you are going to have to be more specific - my VB is limited when it comes to this sort of thing - Are you saying for me to open my word template that contains my macro - insert a new module (module 1 already exists so this will be module 2), create a save and save as sub by typing the above example?
What code would I use for the FileSave sub? - the class displays the form before a document is saved - is this the code to use?

Public WithEvents App As Word.Application
Private Sub App_DocumentBeforeSave(ByVal Doc As Document, SaveAsUI As Boolean, Cancel As Boolean)
UserForm1.Show
End Sub
0
 

Author Comment

by:sbsbats
Comment Utility
Hello,

I have tried the above - this is my code thast almost working - it dosn't crash but when you click save it says "compile error in hidden module: module2" - code =
Sub FileSave()
Private Sub App_DocumentBeforeSave(ByVal Doc As Document, SaveAsUI As Boolean, Cancel As Boolean)
UserForm1.Show
End Sub
0
A Knowledge Base That Stays Up-to-Date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:sbsbats
Comment Utility
Hello again,

The problem is related to the DocumentBeforeSave sub but this is essential as I need the save dialog box to, load after the form selection has been made
0
 
LVL 4

Expert Comment

by:Xcone
Comment Utility
I was just refering to a way to create a Word addin. By creating a Word template containing macro's within the Word Startup folder, it will be detected by Word as an Addin. This removes the need of your macro code to be within normal.dot.
The macro's I posted just have to be inside a module within that addin. They may be in module1, they may be in module2, they may be in module 'MyModule'. What ever pleases your ordering needs. The only requirement is, that they must be publicly available. A module is public by default. So I won't go further in to details on this.
The nature of those macro names, is that they are recognised by Word as AutoExec macro's. So when your macro's are in place, and you save your document, these macro's will be called. This means you can customise the way Word saves. These macro's remove the need for the eventhandler.
In the attached code are 2 samples this time. The 1st is how Word saves the normal way, but then written in macrocode. The 2nd is the FileSaveAs macro the way I believe you need it. You have to put or call your own on the right positions in this. Since I don't know much about window handles and all.
I hope it's more clear this time. Please let me know.

' The way word saves documents by default

Sub FileSave()

  ActiveDocument.Save

End Sub
 

Sub FileSaveAs()

  Dialogs(wdDialogFileSaveAs).Show

End Sub
 

' SaveAs Alternative

Sub FileSaveAs()

  Dim d As Dialog

  

  Set d = Dialogs(wdDialogFileSaveAs)

  

  Load d

  ' Save form is loaded now, you can disable your close button here

  

  If d.Show = vbOK Then

    ' OK button pressed

  Else

    ' Close or Cancel button pressed

  End If

  

  Unload d

End Sub

Open in new window

0
 
LVL 4

Expert Comment

by:Xcone
Comment Utility
I'm really sorry, a screwup on my part. Built-in forms can't be Loaded the it's done in my macro. You have to remove that part. See attached code.

I hope it's still useful for you without the load.
' SaveAs Alternative

Sub FileSaveAs()

  Dim d As Dialog

  

  Set d = Dialogs(wdDialogFileSaveAs)

  

  If d.Show = vbOK Then

    ' OK button pressed

  Else

    ' Close or Cancel button pressed

  End If

  

  Unload d

End Sub

Open in new window

0
 

Author Closing Comment

by:sbsbats
Comment Utility
Cheers, I managed to stumble around and get this going - thanks for steering me in the right direction
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Nice table. Huge mess. Maybe this was something you created way back before you figured out tabs or a document you received from someone else. Either way, using the spacebar to separate the columns resulted in a mess. Trying to convert text to t…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
In this video, we show how to convert an image-only PDF file into a PDF Searchable Image file, that is, a file with both the image (typically from scanning) and text, which is created in an automated fashion with Optical Character Recognition (OCR) …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now