Solved

Creating an AutoExec() macro from existing macro

Posted on 2009-04-05
7
614 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

This is written from a 'VBA for MS Word' perspective, but I am sure it applies to most other MS Office components where VBA is used.  One thing that really bugs me is slow code, ESPECIALLY when it's mine!  In programming there are so many ways to…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
In a previous video Micro Tutorial here at Experts Exchange (http://www.experts-exchange.com/videos/1358/How-to-get-a-free-trial-of-Office-365-with-the-Office-2016-desktop-applications.html), I explained how to get a free, one-month trial of Office …

803 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