Prev: How to hide or disable toolbars and ribbon in word with vba or vb6
Next: Add-InTemplates
From: Stefan Mueller on 9 Mar 2010 19:30
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
From: Stefan Mueller on 10 Mar 2010 08:07
On Mar 10, 5:48 am, Jay Freedman <jay.freed...(a)verizon.net> wrote:
> 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
From: Fumei2 via OfficeKB.com on 10 Mar 2010 15:53
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.

Stefan Mueller wrote:
>> 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

--
Message posted via http://www.officekb.com

From: Fumei2 via OfficeKB.com on 10 Mar 2010 15:57
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.

Fumei2 wrote:
>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.
>
>>> 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.
>[quoted text clipped - 5 lines]
>>Regards
>>Stefan

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/word-programming/201003/1

From: Stefan Mueller on 17 Mar 2010 08:52
On Mar 10, 9:57 pm, "Fumei2 via OfficeKB.com" <u53619(a)uwe> wrote:
> 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
 |  Next  |  Last
Pages: 1 2
Prev: How to hide or disable toolbars and ribbon in word with vba or vb6
Next: Add-InTemplates