How to use VBA to repurpose a built-in command in Word and Excel

3 years ago 394

You tin automate circumstantial tasks by repurposing Word's built-in commands. It requires a spot of VBA, but it's ace easy. Here's how.

The article, How to usage VBA to update fields successful a Word document shows you 3 ways to update fields: printing the nonfiction and 2 VBA procedures. The 2 VBA procedures enactment good together, but you indispensable adjacent oregon unfastened the papers to trigger the procedures. In that article, I promised to amusement you a mode to repurpose built-in commands to update the fields. In this article, I'll amusement you however to repurpose the Save bid to update each fields successful the document. That's a precise circumstantial task, but you tin repurpose astir immoderate built-in bid successful the aforesaid way. We'll beryllium utilizing Word successful the examples, but you tin besides use the aforesaid codification to an Excel file.

SEE: 83 Excel tips each idiosyncratic should master (TechRepublic)

The objection record uses Word fields. If you don't cognize however to usage and participate fields, you mightiness privation to work 3 ways to participate fields successful Microsoft Word. However, the fields are provided successful the objection files, truthful you don't request to cognize however to usage them to implicit the example.

I'm utilizing Microsoft 365 connected a Windows 10 64-bit system, but you tin usage earlier versions. VBA procedures aren't supported successful the web versions. For your convenience, you tin download the objection .docm, .doc and .bas files.

How to adhd the VBA process to Word

If you've created VBA procedures before, you cognize that you enactment successful the Visual Basic Editor . Almost each of Word's built-in commands tin beryllium repurposed to see other tasks utilizing VBA, but the way is simply a spot different.

We'll repurpose the built-in Save bid by adding the indispensable codification to update each fields erstwhile you click Save. You mightiness deliberation the Save As bid much appropriate, but you can't change it. It's portion of the Backstage statement now, and you can't repurpose those commands. However, that doesn't mean you can't get adjacent with Save.

If you're utilizing a ribbon version, beryllium definite to prevention the workbook arsenic a macro-enabled record earlier you continue. If you're utilizing a paper version, you tin skip this step.

Now, let's adhd the process shown successful Listing A (below):

Press Alt + F8.
In the Macros In dropdown list, take Word Commands, arsenic shown successful Figure A.
Thumb down until you find FileSave and prime it. Or participate FileSave. (Commands proceed to statesman with the paper sanction associated with the bid successful the earlier paper version.)
Return to the Macros successful dropdown database and prime the papers oregon template wherever you privation to store this macro. In Figure B, you tin spot that I selected the papers presently unfastened (the objection .docm file).
Click Create to unfastened the VBE with the due stub and command, arsenic shown successful Figure C.

Figure A

Figure B

Figure C

The process contains lone 1 line, the Save method. To repurpose the command, adhd the due code. Listing A includes the VBA bid indispensable to update each fields successful the document. The objection Word .docm record contains this repurposed Save bid (in its NewMacros module).

Listing A

Sub FileSave()

' FileSave Macro

' Saves the progressive papers oregon template

'Update each fields erstwhile closing document; repurpose Save command.

ThisDocument.Fields.Update

ActiveDocument.Save

End Sub

In this case, you tin participate the ThisDocument.Fields.Update line to the Save process you opened earlier if you're moving connected your own. If you participate the codification manually, don't paste that enactment from this web page. Instead, benignant if from the keyboard oregon transcript the enactment into a substance exertion and past paste that codification into the Save procedure. Doing truthful volition region immoderate phantom web characters that mightiness different origin errors.

How to usage the VBA process successful Word

As you tin spot successful Figure D, the objection record comprises 2 pages and each leafage has 2 Word fields, {PAGE} and {NUMPAGES}. If you're moving with the objection .docm, you tin spot the underlying fields by pressing Alt + F9. Press again to fell the codes and amusement the results.

Figure D

To spot however this each works together, delete the hard leafage interruption betwixt the 2 pages. To delete the hard leafage break, presumption the cursor astatine the opening of leafage 2, earlier the connection This. Then, property Backspace. Doing truthful volition region leafage 2 and reposition the substance and fields from leafage 2. As I mentioned, the fields successful the 2nd enactment of substance do not update, arsenic shown in Figure E. This is emblematic of Word fields; astir don't update automatically. (The 2 fields volition update automatically lone successful the header oregon footer.)

Figure E

With some lines connected leafage one, click the Save icon astatine the apical of the surface oregon connected the Quick Access Toolbar. Doing truthful executes the Save command, which present includes the update tract line. Consequently, clicking Save updates the fields, arsenic shown successful Figure F.

Figure F

The repurposed Save bid volition update each fields, and that mightiness not beryllium what you want. In addition, this doesn't see the Save As command, Close, oregon the X (Close) icon. The objection files see a repurposed FileClose process truthful you tin spot however it works, but it's improbable you'll privation to trust connected it.