Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.vba.general,microsoft.public.word.vba.userforms,microsoft.public.word.vba.beginners,microsoft.public.word.tables
TomorrowsMan
 
Posts: n/a
Default Tricky: Word Table Conditional Formatting

Hi there,

I've got a stumper.....

First, I have a Word document with several form fields. Some of these
fields calculate into subsequent bookmarks.

The problem: At the end of the Word document, I have a simple "Overall
Score" table that is 2x4, laid out as such:

Ranking Rating
Fair 0-.99
Good 1-1.99
Great 2-2.99
Excellent 3-4.00

The rating is based on the value of a bookmark (GrandTotal) calculated
by a macro earlier in the document; the rating can be from 0-4 in
half-point increments. So, an average rating would be, for instance,
2.5, which would be a ranking of "Great."

What I need to happen is this: Once the "GrandTotal" bookmark
calculates (which it does OnExit from a previous form field), I would
like the associated row and column in the "Overall Score" table to
highlight; so, if the GrandTotal is 2.5, then "Great" and "2-2.99"
would become boldface text with a light blue background.

I have done this easily and often in Excel with conditional formatting
formulas, but I do not know how to embed the Excel table in Word and
have the conditional formatting run off of the Word table bookmark
"GrandTotal."

Is this possible; or, is there a way to do it with VBA in Word? Or, is
there some way to 'hide' the Word bookmark value is the embedded Excel,
so I can run the conditional formatting in there?

Cheers,
TomorrowsMan

  #2   Report Post  
Posted to microsoft.public.word.vba.general,microsoft.public.word.vba.userforms,microsoft.public.word.vba.beginners,microsoft.public.word.tables
Jezebel
 
Posts: n/a
Default Tricky: Word Table Conditional Formatting

Bookmark each row of your overall score table. In your OnExit macro, use
something like ActiveDocument.Bookmarks("Great").Range.Bold = TRUE




"TomorrowsMan" wrote in message
oups.com...
Hi there,

I've got a stumper.....

First, I have a Word document with several form fields. Some of these
fields calculate into subsequent bookmarks.

The problem: At the end of the Word document, I have a simple "Overall
Score" table that is 2x4, laid out as such:

Ranking Rating
Fair 0-.99
Good 1-1.99
Great 2-2.99
Excellent 3-4.00

The rating is based on the value of a bookmark (GrandTotal) calculated
by a macro earlier in the document; the rating can be from 0-4 in
half-point increments. So, an average rating would be, for instance,
2.5, which would be a ranking of "Great."

What I need to happen is this: Once the "GrandTotal" bookmark
calculates (which it does OnExit from a previous form field), I would
like the associated row and column in the "Overall Score" table to
highlight; so, if the GrandTotal is 2.5, then "Great" and "2-2.99"
would become boldface text with a light blue background.

I have done this easily and often in Excel with conditional formatting
formulas, but I do not know how to embed the Excel table in Word and
have the conditional formatting run off of the Word table bookmark
"GrandTotal."

Is this possible; or, is there a way to do it with VBA in Word? Or, is
there some way to 'hide' the Word bookmark value is the embedded Excel,
so I can run the conditional formatting in there?

Cheers,
TomorrowsMan



  #3   Report Post  
Posted to microsoft.public.word.vba.general,microsoft.public.word.vba.userforms,microsoft.public.word.vba.beginners,microsoft.public.word.tables
TomorrowsMan
 
Posts: n/a
Default Tricky: Word Table Conditional Formatting

Thank you!

I am new at this, and didn't realize I could assign bookmarks to
non-form fields.

So, would I just write a formula into the macro, something like (and
I'm thinking in Excel here, not too keen on VBA yet):

If Val.ActiveDocument.Bookmarks("GrandTotal") 1
ActiveDocument.Bookmarks("Fair").Range.Bold = TRUE
ActiveDocument.Bookmarks("Fair").Range.BackgroundP atternColor =
wdColorBlue

?

And I guess that's my next big stumper; how do I get the OnExit macro
to read the value of the GrandTotal bookmark, pick it out of the ranges
(GrandTotal is less than 1; between 1 and 1.99; between 2 and 2.99; or
over 2.99), then apply the formatting to the corresponding range of
cells?

Thanks again!!

Chris

  #4   Report Post  
Posted to microsoft.public.word.vba.general,microsoft.public.word.vba.userforms,microsoft.public.word.vba.beginners,microsoft.public.word.tables
Jezebel
 
Posts: n/a
Default Tricky: Word Table Conditional Formatting

Select case GrandTotal
Case Is 1
...
Case Is 2
...

etc





"TomorrowsMan" wrote in message
oups.com...
Thank you!

I am new at this, and didn't realize I could assign bookmarks to
non-form fields.

So, would I just write a formula into the macro, something like (and
I'm thinking in Excel here, not too keen on VBA yet):

If Val.ActiveDocument.Bookmarks("GrandTotal") 1
ActiveDocument.Bookmarks("Fair").Range.Bold = TRUE
ActiveDocument.Bookmarks("Fair").Range.BackgroundP atternColor =
wdColorBlue

?

And I guess that's my next big stumper; how do I get the OnExit macro
to read the value of the GrandTotal bookmark, pick it out of the ranges
(GrandTotal is less than 1; between 1 and 1.99; between 2 and 2.99; or
over 2.99), then apply the formatting to the corresponding range of
cells?

Thanks again!!

Chris



  #5   Report Post  
Posted to microsoft.public.word.vba.general,microsoft.public.word.vba.userforms,microsoft.public.word.vba.beginners,microsoft.public.word.tables
TomorrowsMan
 
Posts: n/a
Default Tricky: Word Table Conditional Formatting

Sorry you have been having to lead me by the hand through this; I'm
just discovering how versatile VBA is when making user forms, and I'm
not versed in the language yet.

So, if I take a stab at it.....I would have:

Sub FieldHighlight()
Select case GrandTotal
Case Is 1
ActiveDocument.Bookmarks("Fair").Range.Bold = TRUE
ActiveDocument.Bookmarks("Fair").Range.BackgroundP atternColor =
wdColorBlue
Case Is 2
ActiveDocument.Bookmarks("Good").Range.Bold = TRUE
ActiveDocument.Bookmarks("Good").Range.BackgroundP atternColor =
wdColorBlue
Case Is 3
ActiveDocument.Bookmarks("Great").Range.Bold = TRUE
ActiveDocument.Bookmarks("Great").Range.Background PatternColor =
wdColorBlue
Case Is 4
ActiveDocument.Bookmarks("Excellent").Range.Bold = TRUE
ActiveDocument.Bookmarks("Excellent").Range.Backgr oundPatternColor =
wdColorBlue
End Sub

Is that close? Or am I missing a line selecting the GrandTotal
bookmark?

Thanks again a million times....I'm learning alot, but there's a lot to
learn. :-)

Chris



  #6   Report Post  
Posted to microsoft.public.word.vba.general,microsoft.public.word.vba.userforms,microsoft.public.word.vba.beginners,microsoft.public.word.tables
Jezebel
 
Posts: n/a
Default Tricky: Word Table Conditional Formatting

You'll need to retrieve the value first -- VBA doesn't know what
'GrandTotal' refers to.

Dim pValue as double
Dim pBookmark as string

pValue = ActiveDocument.Bookmarks("GrandTotal").Range
Select case pValue
Case Is 1
pBookmark = "Fair"
:
End Select

With ActiveDocument.Bookmarks(pBookmark).Range
.Bold = True
.BackgroundPatternColor = wdColorBlue
End with


"TomorrowsMan" wrote in message
ups.com...
Sorry you have been having to lead me by the hand through this; I'm
just discovering how versatile VBA is when making user forms, and I'm
not versed in the language yet.

So, if I take a stab at it.....I would have:

Sub FieldHighlight()
Select case GrandTotal
Case Is 1
ActiveDocument.Bookmarks("Fair").Range.Bold = TRUE
ActiveDocument.Bookmarks("Fair").Range.BackgroundP atternColor =
wdColorBlue
Case Is 2
ActiveDocument.Bookmarks("Good").Range.Bold = TRUE
ActiveDocument.Bookmarks("Good").Range.BackgroundP atternColor =
wdColorBlue
Case Is 3
ActiveDocument.Bookmarks("Great").Range.Bold = TRUE
ActiveDocument.Bookmarks("Great").Range.Background PatternColor =
wdColorBlue
Case Is 4
ActiveDocument.Bookmarks("Excellent").Range.Bold = TRUE
ActiveDocument.Bookmarks("Excellent").Range.Backgr oundPatternColor =
wdColorBlue
End Sub

Is that close? Or am I missing a line selecting the GrandTotal
bookmark?

Thanks again a million times....I'm learning alot, but there's a lot to
learn. :-)

Chris



  #7   Report Post  
Posted to microsoft.public.word.vba.general,microsoft.public.word.vba.userforms,microsoft.public.word.vba.beginners,microsoft.public.word.tables
TomorrowsMan
 
Posts: n/a
Default Tricky: Word Table Conditional Formatting

Thank you so very much!

Cheers!

  #8   Report Post  
Posted to microsoft.public.word.vba.general,microsoft.public.word.vba.userforms,microsoft.public.word.vba.beginners,microsoft.public.word.tables
macropod
 
Posts: n/a
Default Tricky: Word Table Conditional Formatting

Hi TomorrowsMan,

You can also do this without vba, using formula fields. For example, press
Ctrl-F9 three times to create a set of three nested fields:
{ { { } } }
then embed more fields within these so that you get:
{ { } { } { { } { } { { } { } { } }}}
and fill them out so that they look like:
{IF{GrandTotal} 1 "{GrandTotal \# 0.00} Fair" {IF{GrandTotal} 2
"{GrandTotal \# 0.00} Good" {IF{GrandTotal} 3 "{GrandTotal \# 0.00} Great"
"{GrandTotal \# 0.00} Excellent"}}}
then apply the desired formatting to each of the output strings (eg
"{GrandTotal \# 0.00} Good").

Now, when you update the 'GrandTotal', the output will display in the
desired format. You can nest up to 20 IF tests this way in Word.

Cheers


"TomorrowsMan" wrote in message
oups.com...
Hi there,

I've got a stumper.....

First, I have a Word document with several form fields. Some of these
fields calculate into subsequent bookmarks.

The problem: At the end of the Word document, I have a simple "Overall
Score" table that is 2x4, laid out as such:

Ranking Rating
Fair 0-.99
Good 1-1.99
Great 2-2.99
Excellent 3-4.00

The rating is based on the value of a bookmark (GrandTotal) calculated
by a macro earlier in the document; the rating can be from 0-4 in
half-point increments. So, an average rating would be, for instance,
2.5, which would be a ranking of "Great."

What I need to happen is this: Once the "GrandTotal" bookmark
calculates (which it does OnExit from a previous form field), I would
like the associated row and column in the "Overall Score" table to
highlight; so, if the GrandTotal is 2.5, then "Great" and "2-2.99"
would become boldface text with a light blue background.

I have done this easily and often in Excel with conditional formatting
formulas, but I do not know how to embed the Excel table in Word and
have the conditional formatting run off of the Word table bookmark
"GrandTotal."

Is this possible; or, is there a way to do it with VBA in Word? Or, is
there some way to 'hide' the Word bookmark value is the embedded Excel,
so I can run the conditional formatting in there?

Cheers,
TomorrowsMan



  #9   Report Post  
Posted to microsoft.public.word.vba.general,microsoft.public.word.vba.userforms,microsoft.public.word.vba.beginners,microsoft.public.word.tables
Jezebel
 
Posts: n/a
Default Tricky: Word Table Conditional Formatting

Hilarious.



"macropod" wrote in message
...
Hi TomorrowsMan,

You can also do this without vba, using formula fields. For example, press
Ctrl-F9 three times to create a set of three nested fields:
{ { { } } }
then embed more fields within these so that you get:
{ { } { } { { } { } { { } { } { } }}}
and fill them out so that they look like:
{IF{GrandTotal} 1 "{GrandTotal \# 0.00} Fair" {IF{GrandTotal} 2
"{GrandTotal \# 0.00} Good" {IF{GrandTotal} 3 "{GrandTotal \# 0.00}
Great"
"{GrandTotal \# 0.00} Excellent"}}}
then apply the desired formatting to each of the output strings (eg
"{GrandTotal \# 0.00} Good").

Now, when you update the 'GrandTotal', the output will display in the
desired format. You can nest up to 20 IF tests this way in Word.

Cheers


"TomorrowsMan" wrote in message
oups.com...
Hi there,

I've got a stumper.....

First, I have a Word document with several form fields. Some of these
fields calculate into subsequent bookmarks.

The problem: At the end of the Word document, I have a simple "Overall
Score" table that is 2x4, laid out as such:

Ranking Rating
Fair 0-.99
Good 1-1.99
Great 2-2.99
Excellent 3-4.00

The rating is based on the value of a bookmark (GrandTotal) calculated
by a macro earlier in the document; the rating can be from 0-4 in
half-point increments. So, an average rating would be, for instance,
2.5, which would be a ranking of "Great."

What I need to happen is this: Once the "GrandTotal" bookmark
calculates (which it does OnExit from a previous form field), I would
like the associated row and column in the "Overall Score" table to
highlight; so, if the GrandTotal is 2.5, then "Great" and "2-2.99"
would become boldface text with a light blue background.

I have done this easily and often in Excel with conditional formatting
formulas, but I do not know how to embed the Excel table in Word and
have the conditional formatting run off of the Word table bookmark
"GrandTotal."

Is this possible; or, is there a way to do it with VBA in Word? Or, is
there some way to 'hide' the Word bookmark value is the embedded Excel,
so I can run the conditional formatting in there?

Cheers,
TomorrowsMan





Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Word 97 in Windows XP to maintain formatting Charlie''s Word VBA questions Microsoft Word Help 22 May 20th 23 08:51 PM
Word should allow to 'divide' page by 3 or 4, not just 2 CandasK Page Layout 2 February 28th 06 10:16 PM
WP merge file to Word sstires Tables 4 February 14th 06 06:26 PM
How can Word display full path of a file in the title bar? SAsif Microsoft Word Help 1 January 26th 06 04:32 PM
Word applies direct format on File open Uriel Microsoft Word Help 16 November 27th 05 07:22 PM


All times are GMT +1. The time now is 12:13 AM.

Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"