Thank you both for your reply. They were quite useful.
As Graham noted, my verbiage was a bit ambiguous. Part
of this stems from my not being entirely familiar with Word
terminology, part of it from not being entirely familiar with
exactly what I wanted (or the form that it should take). In
essence, in the absence of an explicit setting to do what I
wanted (which was a visual indication of when I still need
to fill something out: a validator with visual feedback), I was
looking for an 'on change' mechanism as could be found on
web pages.
What I found, springboarding off your responses, was that
Word is event poor, especially as it relates to changes within
the document. In fact, I can see only one event that offers
notification in this area:
WindowSelectionChange(ByVal Sel As Selection)
Unfortunately, this function does not provide information on
the previous selection (which, a priori, seems like a somewhat
poor design decision). That is, it does not provide information
on what (or even the selection) that just changed. That means
that to identify what changed, in order to be able to set the
background color, the entire document must be trawled through.
Nevertheless, for my purposes it suffices because of two important
reasons: 1) I have relatively few cells to check for change.
Therefore, such a check is reasonably fast.
2) The event handler does not fire upon typing of text characters.
This may seem odd, at first, but in a way it was quite clever of
the word designers (depending on what one wishes to accomplish).
Non text character manipulation in Word (such as a mouse
movement or arrow key or end, home, etc. time-expensive
compared to typeing characters. Therefore, in principle, this
event handler, if tightly written, should not bog down in standard
word usage. Nice.
So, here is a working example, based off of
http://msdn.microsoft.com/en-us/library/aa140279(office.10).aspx
and Graham and Peter's comments: The example is meant to
be included with the Word document that it applies to, so it
won't pollute other Word documents.
To see it working, create and save a new Word document with a
single table with at least 3 columns and at least 2 rows. Now bring
up the VBA editor (Alt+F11 or \Tools\Macros\Visual Basic Editor)
and \Insert\Class Module. Under Properties, change the name to:
clsEventHandler
Now insert the following into the Code pane:
Public WithEvents ChangeMonitor As Word.Application
Private aCellList As Variant
Private cellEnd As String
Private Sub ChangeMonitor_WindowSelectionChange( _
ByVal Sel As Selection)
Dim i, tbl As Table, cl As cell, clText
For i = LBound(aCellList) To _
UBound(aCellList) - 2 Step 3
Set tbl = Sel.Document.Tables(aCellList(i))
Set cl = tbl.cell(aCellList(i + 1), _
aCellList(i + 2))
clText = cl.Range.Text
If Len(clText) > 1 Then
If Mid(clText, Len(clText) - 1) = cellEnd Then _
clText = Mid(clText, 1, Len(clText) - 2)
End If
If (clText = "") <> _
(cl.Shading.BackgroundPatternColor = _
wdColorYellow) Then
If clText = "" Then _
cl.Shading.BackgroundPatternColor = _
wdColorYellow Else _
cl.Shading.BackgroundPatternColor = _
wdColorAutomatic
End If
Next i
End Sub
Private Sub Class_Initialize()
aCellList = Array(1, 2, 3, 1, 1, 3)
cellEnd = Chr(13) & Chr(7)
ChangeMonitor_WindowSelectionChange _
Application.ActiveDocument.ActiveWindow.Selection
End Sub
Now within the VBA editor, \Insert\Module, optionally
give it a name (such as modHandleEvents), and put
the following code in it:
Dim objEventHandler As clsEventHandler
Sub CreateEventHandler()
Set objEventHandler = New clsEventHandler
Set objEventHandler.ChangeMonitor = Word.Application
End Sub
Sub DestroyEventHandler()
Set objEventHandler = Nothing
End Sub
Finally, again within the VBA editor, double click on
ThisDocument under Microsoft Word Objects and insert
the following code:
Private Sub Document_Close()
DestroyEventHandler
End Sub
Private Sub Document_Open()
CreateEventHandler
End Sub
Now Save and Close the Word document, then open
it up again. You will see, in garish yellow color, that
the first two cells in the third column of the first table are
colored yellow, but when you enter text (and subsequently
move the cursor by any means (hence changing the
selection)) the color will be cleared. In my simple tests,
this is working fine.
Thanks again for your pointers,
Csaba Gabor from Vienna
original thread:
http://groups.google.com/group/microsoft.public.word.vba.general/browse_frm/thread/71147c5ebd6699c7
Word events compendium:
http://word.mvps.org/FAQs/General/CommandsList.htm
PS. I think one can also trap for keystrokes of individual
keys in Word, so that might be an option in one wants to
extend even greater control, but I would go there with
trepidation on account of worry about bogging Word down.
If you are simply entering text in empty coloured cells and not in a
protected form
I suspect you could also achieve a macro-free version by putting a
{ MACROBUTTON NoMacro [Yellow]}
field in each cell, where [Yellow] is a suitable yellow image or piece
of text. It won't fill the cell of course, but perhaps all that is
required is to have a yellow marker (cf. a strip of post-it note).
But you may have gone down that route and come across reasons why it
won't work. The main drawback AFAICS is that the user can still click at
either end of the button and type text that does not replace the
MACROBUTTON, so if more control is needed, this would never be enough.
However, if they tab in or click, it seems to do the job.
In Word 2007 using a yellow image didn't seem to work very well, but a
line of non-space characters, e.g. "x" coloured yellow and highlighted
yellow seems to work OK. Perhaps there's a much simpler way.
(For the original poster, you would create this field as follows:
a. put the insertion point somewhere outside the table (so it is
easier to work)
b. use ctrl-F9 to insert a pair of the special field braces { }
c. Type MACROBUTTON NoMacro xxxxxx so you have
{ MACROBUTTON NoMacro xxxxxx }
d. Select the xxxxxx and colour the text yellow and highlight it yellow
e. delete the space after the final x so you have
{ MACROBUTTON NoMacro xxxxxx}
f. select the field, and use F9 to execute it. Nothing visible is
likely to happen.
g. Copy/paste the field into a table cell
h. Use Alt-F9 to toggle the field codes until you see the field result
rather than the field text.
)
Peter Jamieson
http://tips.pjmsn.me.uk
Your subject line refers to 'fields' yet the text of your message refers to
cell contents. If you are using form fields, you can force the user to fill
fields by validating those fields with macros (assuming of course that the
user will allow the macros) - seehttp://
www.gmayor.com/formfieldmacros.htm
You can also use the exit macro to unlock the form, change the cell
background shading colour and lock the form again.
If you are simply entering text in empty coloured cells and not in a
protected form, you can use the NextCell command to format the cells. In the
following example the macro checks for content in cell row 1 column 1 and if
the cell is empty it will have a range length of 2, so when you tab out of
the cell, if nothing is entered in it, the cell remains yellow. You do
however have to both allow the macro to run and the user must tab between
cells. If you need it more automatic that that you will need to insert an
Excel table
Sub NextCell()
With ActiveDocument.Tables(1)
With .Cell(1, 1)
If Len(.Range.Text) = 2 Then
.Shading.BackgroundPatternColor = wdColorYellow
Else
.Shading.BackgroundPatternColor = wdColorAutomatic
End If
End With
End With
Selection.MoveRight Unit:=wdCell
End Sub
