How to figure out if a key is pressed in a cell of a table or a cellof a table lost the focus

[Stefan Mueller]

I've a table in Microsoft Word (Office 2003) with several rows and
columns.
Like in Excel I'd like to color the cells according to their values
(called conditional formating in Microsoft Excel). If the value is
less than zero the cell gets colored red, is it zero the cell gets
colored yellow and is it greater than zero the cell gets colored
green.

My problem with VBA is how to figure out if a cell got edited.
In Visual Basic I have events like LostFocus or KeyDown. But I'm
missing this events in VBA.

Any help is very appreciated.

Regards
Stefan

 

[Jay Freedman]

Look at the last post in the thread
http://www.eggheadcafe.com/software/aspnet/31328183/change-background-to-red.aspx.
As background for it, you'll find
http://word.mvps.org/FAQs/MacrosVBA/AppClassEvents.htm is useful.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

 

[Stefan Mueller]

Look at the last post in the threadhttp://www.eggheadcafe.com/software/aspnet/31328183/change-background....
As background for it, you'll findhttp://word.mvps.org/FAQs/MacrosVBA/AppClassEvents.htmis useful.

Many thanks for the links.
Unfortunately, with WindowSelectionChange I only get the new selection
and not the selection before the mouse click.

Regards
Stefan

 

[Fumei2 via OfficeKB.com]

That is correct. WindowsSelectionChange means just that.

What, exactly, are you trying to do? Make this a sort of real-time event?
Or, do you want to process a table, and determine its current values, and
color cells accordingly?

If you want a real-time event, use Excel. It is both easier, and better.

“Like in Excel I'd like to color the cells according to their values (called
conditional formating in Microsoft Excel).â€

Precisely. Excel does this, Word does not. Word is not really designed for
this (although you can sort of achieve it). On the other hand, if you simply
want to process tables and color things as they are, then this is quite do-
able.

Function CellText2(strIn As String)
CellText2 = Left(strIn, Len(strIn) - 2)
End Function

Sub ColorMe()
Dim oTable As Table
Dim oCell As Cell
Dim strCell As String
Dim check

For Each oTable In ActiveDocument.Tables
For Each oCell In oTable.Range.Cells
strCell = CellText2(oCell.Range.Text)
check = IsNumeric(strCell)
If check = True Then
Select Case strCell
Case Is < 0
oCell.Shading.BackgroundPatternColor = wdColorRed
Case 0
oCell.Shading.BackgroundPatternColor = wdColorGold
Case Is > 0
oCell.Shading.BackgroundPatternColor = wdColorGreen
End Select
End If
Next
Next
End Sub

This goes through all the tables - you could make it that it processes only
one if you want - checks to see if the cell content is numeric (you do not
want to process text I assume) - and colors things accordingly.

 

[Fumei2 via OfficeKB.com]

Clicked too fast.

It is not real-time, but it does process all the tables at once, and since
there is no Selection, it is pretty darn fast.

That is correct. WindowsSelectionChange means just that.

What, exactly, are you trying to do? Make this a sort of real-time event?
Or, do you want to process a table, and determine its current values, and
color cells accordingly?

If you want a real-time event, use Excel. It is both easier, and better.

“Like in Excel I'd like to color the cells according to their values (called
conditional formating in Microsoft Excel).â€

Precisely. Excel does this, Word does not. Word is not really designed for
this (although you can sort of achieve it). On the other hand, if you simply
want to process tables and color things as they are, then this is quite do-
able.

Function CellText2(strIn As String)
CellText2 = Left(strIn, Len(strIn) - 2)
End Function

Sub ColorMe()
Dim oTable As Table
Dim oCell As Cell
Dim strCell As String
Dim check

For Each oTable In ActiveDocument.Tables
For Each oCell In oTable.Range.Cells
strCell = CellText2(oCell.Range.Text)
check = IsNumeric(strCell)
If check = True Then
Select Case strCell
Case Is < 0
oCell.Shading.BackgroundPatternColor = wdColorRed
Case 0
oCell.Shading.BackgroundPatternColor = wdColorGold
Case Is > 0
oCell.Shading.BackgroundPatternColor = wdColorGreen
End Select
End If
Next
Next
End Sub

This goes through all the tables - you could make it that it processes only
one if you want - checks to see if the cell content is numeric (you do not
want to process text I assume) - and colors things accordingly.
[quoted text clipped - 5 lines]

Regards
Stefan

 

[Stefan Mueller]

It is not real-time, but it does process all the tables at once, and since
there is no Selection, it is pretty darn fast.

Many thanks for your code. It works fine but like mentioned it's not
real-time.

I know that Excel is designed (and not Word) to do stuff like
conditional formating. However, I do need it within Microsoft Word.
I have text documents with several Word tables and I'd like to color
the cells according to their entries in real-time.

Is there really no possibility to get an event if an entry of a cell
is changed or the cell is left so that I can color the left cell?
If not then I could trigger your posted code e.g. every 5 seconds
which would do the job but not be so nice.

Regards
Stefan

 

[Stefan Mueller]

Is there really no possibility to get an event in Word if an entry of
a cell of a table is changed or the cell is left?

Regards
Stefan