Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.vba.general,microsoft.public.word.vba.userforms,microsoft.public.word.vba.beginners,microsoft.public.word.tables
|
|||
|
|||
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
Posted to microsoft.public.word.vba.general,microsoft.public.word.vba.userforms,microsoft.public.word.vba.beginners,microsoft.public.word.tables
|
|||
|
|||
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
Posted to microsoft.public.word.vba.general,microsoft.public.word.vba.userforms,microsoft.public.word.vba.beginners,microsoft.public.word.tables
|
|||
|
|||
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
Posted to microsoft.public.word.vba.general,microsoft.public.word.vba.userforms,microsoft.public.word.vba.beginners,microsoft.public.word.tables
|
|||
|
|||
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
Posted to microsoft.public.word.vba.general,microsoft.public.word.vba.userforms,microsoft.public.word.vba.beginners,microsoft.public.word.tables
|
|||
|
|||
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
Posted to microsoft.public.word.vba.general,microsoft.public.word.vba.userforms,microsoft.public.word.vba.beginners,microsoft.public.word.tables
|
|||
|
|||
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
Posted to microsoft.public.word.vba.general,microsoft.public.word.vba.userforms,microsoft.public.word.vba.beginners,microsoft.public.word.tables
|
|||
|
|||
Tricky: Word Table Conditional Formatting
Thank you so very much!
Cheers! |
#8
Posted to microsoft.public.word.vba.general,microsoft.public.word.vba.userforms,microsoft.public.word.vba.beginners,microsoft.public.word.tables
|
|||
|
|||
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
Posted to microsoft.public.word.vba.general,microsoft.public.word.vba.userforms,microsoft.public.word.vba.beginners,microsoft.public.word.tables
|
|||
|
|||
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 | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Word 97 in Windows XP to maintain formatting | Microsoft Word Help | |||
Word should allow to 'divide' page by 3 or 4, not just 2 | Page Layout | |||
WP merge file to Word | Tables | |||
How can Word display full path of a file in the title bar? | Microsoft Word Help | |||
Word applies direct format on File open | Microsoft Word Help |