How do I go about calling a userform in VBA when user clicks on the Save button in MS Word?
Best Answer
You have two options to do that: You can either override the built-in FileSave and FileSaveAs commands, or you can create an event handler for the application's DocumentBeforeSave event (which is a little more work to do).
Overriding the built-in commands can be accomplished by adding the following code to a VBA module (adjust the type of the user form to be displayed accordingly):
' override File -> Save
Public Sub FileSave()
CustomSave
' call ActiveDocument.Save to actually save the document
End Sub
' override File -> Save As...
Public Sub FileSaveAs()
CustomSave
' call ActiveDocument.SaveAs to actually save the document
End Sub
Sub CustomSave()
Dim frm As New frmCustomSave
frm.Show
End Sub
The second option can be implemented by placing the following code under Microsoft Word Objects -> ThisDocument in the VBA editor:
Option Explicit
Private WithEvents wdApp As Word.Application
Private Sub Document_New()
Set wdApp = Word.Application
End Sub
Private Sub Document_Open()
Set wdApp = Word.Application
End Sub
Private Sub wdApp_DocumentBeforeSave(ByVal Doc As Document, SaveAsUI As Boolean, Cancel As Boolean)
Dim frm As New frmCustomSave
frm.Show
End Sub
As specified by the top answer, I used the following in the code behind the button control.
Private Sub btnClose_Click()
Unload Me
End Sub
In doing so, it will not attempt to unload a control, but rather will unload the user form where the button control resides. The "Me" keyword refers to the user form object even when called from a control on the user form. If you are getting errors with this technique, there are a couple of possible reasons.
You could be entering the code in the wrong place (such as a
separate module)
You might be using an older version of Office. I'm using Office 2013. I've noticed that VBA changes over time.
From my experience, the use of the the DoCmd.... method is more specific to the macro features in MS Access, but not commonly used in Excel VBA.
Under normal (out of the box) conditions, the code above should work just fine.
Best Answer
You have two options to do that: You can either override the built-in
FileSaveandFileSaveAscommands, or you can create an event handler for the application'sDocumentBeforeSaveevent (which is a little more work to do).Overriding the built-in commands can be accomplished by adding the following code to a VBA module (adjust the type of the user form to be displayed accordingly):
The second option can be implemented by placing the following code under Microsoft Word Objects -> ThisDocument in the VBA editor: