Solved

Creating an AutoExec() macro from existing macro

Posted on 2009-04-05
7
609 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
ID: 24075514
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
ID: 24082583
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
ID: 24083500
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:sbsbats
ID: 24083817
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
ID: 24084456
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
ID: 24084477
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
ID: 31566878
Cheers, I managed to stumble around and get this going - thanks for steering me in the right direction
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Shortcuts in Word Just the other day I had a training for Microsoft and they wanted me to show how well the new Windows and Office behaved on a touch device, which by the way is great, but it was only then that I realized that using keyboard shortc…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
This video walks the viewer through the process of creating a watermark for their document, customizing it, and saving it for viewing/printing needs.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

911 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

19 Experts available now in Live!

Get 1:1 Help Now