On 25-26 February 2021 I taught a series of courses on VBA programming. The first four courses were about using VBA in Excel and then in the afternoon of 26 February we finished with a course about using VBA in other applications.
Exercise 2 was about making a filter that would trap inappropriate words in a list and highlight them in red. The procedure was triggered by the DocumentBeforePrint event.
The exercise was a little tricky and intricate and some participants had difficulty making the procedure work completely. Below I attach the main section of VBA code that was used.
For participants on this course please either open the Sandbox tool from your MyLearningTree account or build your own local copy using the instructions in course D140 from page 43 of the Course Notes.
The code below should be pasted into the ThisDocument window.
Option Explicit
Private strBads() As String
Private WithEvents appRef As Application
Public Sub AutoExec()
Set appRef = Application
ReDim strBads(4)
strBads(0) = "damn"
strBads(1) = "fool"
strBads(2) = "stupid"
strBads(3) = "moron"
strBads(4) = "idiot"
End Sub
Private Function FindWords(docAny As Document) As Boolean
Dim rngWord As Range
Dim i As Integer
For Each rngWord In docAny.Words
For i = 0 To UBound(strBads)
If InStr(LCase(rngWord), strBads(i)) > 0 Then
rngWord.Font.Color = vbRed
FindWords = True
End If
Next
Next
End Function
Private Sub appRef_DocumentBeforePrint(ByVal Doc As Document, Cancel As Boolean)
Dim intAnswer As Integer
If FindWords(Doc) = True Then
Application.ScreenRefresh
intAnswer = MsgBox("Bad words found, print?", vbYesNo + vbExclamation)
If intAnswer = vbNo Then Cancel = True
End If
End Sub