Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bolding using VBA
The project that I'm working has the following structure If item A is true - show sentence 1 If item B is true - show sentence 2 if item C is true - show sentence 3 .. .. .. The list item is always true and show its sentence and make it bold (for this example lets assume that there are 45 characters is this sentence). I have the logic built to show only the "true" items. The "twist" is that these sentences need to be in paragraph form (therefore I have a macro that will "fill-justify these sentences into paragraph form and it works). Therefore, the last sentence could be in the last line or the last two lines. Is the best way to make sure the I bold the right text as follows: Start with the last row - count how many characters are in it. If it has more characters that the sentence that I'm concered about activecell.characters(2,45).font.bold = True (this assumes the last sentence is would start in position 2. If the last row as less characters than 45 characters bold the last row. - move up a row and bold the last (45-number of characters from row below) characters. I'm assuming that this will work or is there a better way to do this? Or am I not making sense? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bolding using VBA
Brad,
Perhaps some sample data, the logical test to be applied and an example of the desired output may help in your quest for assistance. Mike "Brad" wrote: The project that I'm working has the following structure If item A is true - show sentence 1 If item B is true - show sentence 2 if item C is true - show sentence 3 . . . The list item is always true and show its sentence and make it bold (for this example lets assume that there are 45 characters is this sentence). I have the logic built to show only the "true" items. The "twist" is that these sentences need to be in paragraph form (therefore I have a macro that will "fill-justify these sentences into paragraph form and it works). Therefore, the last sentence could be in the last line or the last two lines. Is the best way to make sure the I bold the right text as follows: Start with the last row - count how many characters are in it. If it has more characters that the sentence that I'm concered about activecell.characters(2,45).font.bold = True (this assumes the last sentence is would start in position 2. If the last row as less characters than 45 characters bold the last row. - move up a row and bold the last (45-number of characters from row below) characters. I'm assuming that this will work or is there a better way to do this? Or am I not making sense? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bolding using VBA
Listed below are 5 situations that could occur: These sentences have to be
put into paragraph form. Also here is my VBA, it works excpet that it is bolding both the last and second to last sentences (after it is reformated) Please note regardless of where "Employer contributions, if any, are not reflected in the illustration." shows up it must be bolded. Sub Bradstest1() Dim RemainingBold As Integer Dim SecondSentence As Integer shtPrem.Range("o12:o" & 12 + Range("d1").Value).Value = shtPrem.Range(Range("b1").Value).Value shtPrem.Range("o12:x22").Justify shtPrem.Range("o" & 12 + Range("d1").Value).Font.Bold = True RemainingBold = 70 - Len(shtPrem.Range("o" & 12 + Range("d1").Value)) SecondSentence = Len(shtPrem.Range("o" & 11 + Range("d1").Value)) If Len(shtPrem.Range("o" & 12 + Range("d1").Value)) < 70 Then shtPrem.Range("o" & 11 + Range("d1").Value).Characters((SecondSentence - RemainingBold), RemainingBold).Font.Bold = True End If End Sub This illustration assumes an annual premium amount of $1,000.00 based on an annual payment mode. The values shown in the illustration assume that the premiums are paid at the beginning of the payment period. In addition, there is a single premium deposit of $999,999.99 on mm/dd/yyyy. The illustration shows total annual premiums assumed. Values will vary depending on the timing of premium payments. Employer contributions, if any, are not reflected in the illustration. This illustration assumes an annual premium amount of $1,000.00 based on an annual payment mode. The values shown in the illustration assume that the premiums are paid at the beginning of the payment period. In addition, this illustration assumes XX single premium deposits between mm/dd/yyyy and the assumed retirement date of mm/dd/yyyy. The illustration shows total annual premiums assumed. The amount and timing of these future single premium deposits are detailed on page 3 of this illustration. Values will vary depending on the timing of premium payments. Employer contributions, if any, are not reflected in the illustration. This illustration assumes an annual premium amount of $1,000.00 based on an annual payment mode. The values shown in the illustration assume that the premiums are paid at the beginning of the payment period. The illustration shows total annual premiums assumed. Values will vary depending on the timing of premium payments. Employer contributions, if any, are not reflected in the illustration. A single premium deposit of $999,999.99 on mm/dd/yyyy is assumed. Values will vary depending on the timing of premium payments. Employer contributions, if any, are not reflected in the illustration. Thiss illustration assumes XX single premium deposits between mm/dd/yyyy and the assumed retirement date of MM/DD/YYYY. The amount and timing of these future single premium deposits are detailed on page 3 of this illustration. Values will vary depending on the timing of premium payments. Employer contributions, if any, are not reflected in the illustration. "Mike H" wrote: Brad, Perhaps some sample data, the logical test to be applied and an example of the desired output may help in your quest for assistance. Mike "Brad" wrote: The project that I'm working has the following structure If item A is true - show sentence 1 If item B is true - show sentence 2 if item C is true - show sentence 3 . . . The list item is always true and show its sentence and make it bold (for this example lets assume that there are 45 characters is this sentence). I have the logic built to show only the "true" items. The "twist" is that these sentences need to be in paragraph form (therefore I have a macro that will "fill-justify these sentences into paragraph form and it works). Therefore, the last sentence could be in the last line or the last two lines. Is the best way to make sure the I bold the right text as follows: Start with the last row - count how many characters are in it. If it has more characters that the sentence that I'm concered about activecell.characters(2,45).font.bold = True (this assumes the last sentence is would start in position 2. If the last row as less characters than 45 characters bold the last row. - move up a row and bold the last (45-number of characters from row below) characters. I'm assuming that this will work or is there a better way to do this? Or am I not making sense? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bolding using VBA
Two things
First of all Sub Bradstest1() Dim RemainingBold As Integer Dim SecondSentence As Integer shtPrem.Range("o12:o" & 12 + Range("d1").Value).Value = shtPrem.Range(Range("b1").Value).Value shtPrem.Range("o12:x22").Justify shtPrem.Range("o" & 12 + Range("d1").Value).Font.Bold = True RemainingBold = 70 - Len(shtPrem.Range("o" & 12 + Range("d1").Value)) SecondSentence = Len(shtPrem.Range("o" & 11 + Range("d1").Value)) If Len(shtPrem.Range("o" & 12 + Range("d1").Value)) < 70 Then shtPrem.Range("o" & 11 + Range("d1").Value).Characters(SecondSentence - RemainingBold, RemainingBold).Font.Bold = True End If End Sub works - I just have to make sure the each cell is not bolded before I start. But is there a better way to do this. Just remember that I'm going to have several different sentences that I'm going to put together (using Fill-Justity) and then bolding the last sentence........... "Brad" wrote: Listed below are 5 situations that could occur: These sentences have to be put into paragraph form. Also here is my VBA, it works excpet that it is bolding both the last and second to last sentences (after it is reformated) Please note regardless of where "Employer contributions, if any, are not reflected in the illustration." shows up it must be bolded. Sub Bradstest1() Dim RemainingBold As Integer Dim SecondSentence As Integer shtPrem.Range("o12:o" & 12 + Range("d1").Value).Value = shtPrem.Range(Range("b1").Value).Value shtPrem.Range("o12:x22").Justify shtPrem.Range("o" & 12 + Range("d1").Value).Font.Bold = True RemainingBold = 70 - Len(shtPrem.Range("o" & 12 + Range("d1").Value)) SecondSentence = Len(shtPrem.Range("o" & 11 + Range("d1").Value)) If Len(shtPrem.Range("o" & 12 + Range("d1").Value)) < 70 Then shtPrem.Range("o" & 11 + Range("d1").Value).Characters((SecondSentence - RemainingBold), RemainingBold).Font.Bold = True End If End Sub This illustration assumes an annual premium amount of $1,000.00 based on an annual payment mode. The values shown in the illustration assume that the premiums are paid at the beginning of the payment period. In addition, there is a single premium deposit of $999,999.99 on mm/dd/yyyy. The illustration shows total annual premiums assumed. Values will vary depending on the timing of premium payments. Employer contributions, if any, are not reflected in the illustration. This illustration assumes an annual premium amount of $1,000.00 based on an annual payment mode. The values shown in the illustration assume that the premiums are paid at the beginning of the payment period. In addition, this illustration assumes XX single premium deposits between mm/dd/yyyy and the assumed retirement date of mm/dd/yyyy. The illustration shows total annual premiums assumed. The amount and timing of these future single premium deposits are detailed on page 3 of this illustration. Values will vary depending on the timing of premium payments. Employer contributions, if any, are not reflected in the illustration. This illustration assumes an annual premium amount of $1,000.00 based on an annual payment mode. The values shown in the illustration assume that the premiums are paid at the beginning of the payment period. The illustration shows total annual premiums assumed. Values will vary depending on the timing of premium payments. Employer contributions, if any, are not reflected in the illustration. A single premium deposit of $999,999.99 on mm/dd/yyyy is assumed. Values will vary depending on the timing of premium payments. Employer contributions, if any, are not reflected in the illustration. Thiss illustration assumes XX single premium deposits between mm/dd/yyyy and the assumed retirement date of MM/DD/YYYY. The amount and timing of these future single premium deposits are detailed on page 3 of this illustration. Values will vary depending on the timing of premium payments. Employer contributions, if any, are not reflected in the illustration. "Mike H" wrote: Brad, Perhaps some sample data, the logical test to be applied and an example of the desired output may help in your quest for assistance. Mike "Brad" wrote: The project that I'm working has the following structure If item A is true - show sentence 1 If item B is true - show sentence 2 if item C is true - show sentence 3 . . . The list item is always true and show its sentence and make it bold (for this example lets assume that there are 45 characters is this sentence). I have the logic built to show only the "true" items. The "twist" is that these sentences need to be in paragraph form (therefore I have a macro that will "fill-justify these sentences into paragraph form and it works). Therefore, the last sentence could be in the last line or the last two lines. Is the best way to make sure the I bold the right text as follows: Start with the last row - count how many characters are in it. If it has more characters that the sentence that I'm concered about activecell.characters(2,45).font.bold = True (this assumes the last sentence is would start in position 2. If the last row as less characters than 45 characters bold the last row. - move up a row and bold the last (45-number of characters from row below) characters. I'm assuming that this will work or is there a better way to do this? Or am I not making sense? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bolding using VBA
I think that this is better
Sub ReformatSentences() Dim RemainingBold As Integer Dim SecondSentence As Integer Dim LastSentence As Integer shtPrem.Range("o12:o" & 12 + Range("d1").Value).Value = shtPrem.Range(Range("b1").Value).Value shtPrem.Range("o12:x22").Justify SecondSentence = Len(shtPrem.Range("o" & 11 + Range("d1").Value)) LastSentence = Len(shtPrem.Range("o" & 12 + Range("d1").Value)) RemainingBold = Application.Max(0, 70 - LastSentence) If RemainingBold 0 Then shtPrem.Range("o" & 11 + Range("d1").Value).Characters(SecondSentence - RemainingBold, RemainingBold).Font.Bold = True shtPrem.Range("o" & 12 + Range("d1").Value).Font.Bold = True Else shtPrem.Range("o" & 12 + Range("d1").Value).Characters(LastSentence - 70, 70).Font.Bold = True End If End Sub "Brad" wrote: Listed below are 5 situations that could occur: These sentences have to be put into paragraph form. Also here is my VBA, it works excpet that it is bolding both the last and second to last sentences (after it is reformated) Please note regardless of where "Employer contributions, if any, are not reflected in the illustration." shows up it must be bolded. Sub Bradstest1() Dim RemainingBold As Integer Dim SecondSentence As Integer shtPrem.Range("o12:o" & 12 + Range("d1").Value).Value = shtPrem.Range(Range("b1").Value).Value shtPrem.Range("o12:x22").Justify shtPrem.Range("o" & 12 + Range("d1").Value).Font.Bold = True RemainingBold = 70 - Len(shtPrem.Range("o" & 12 + Range("d1").Value)) SecondSentence = Len(shtPrem.Range("o" & 11 + Range("d1").Value)) If Len(shtPrem.Range("o" & 12 + Range("d1").Value)) < 70 Then shtPrem.Range("o" & 11 + Range("d1").Value).Characters((SecondSentence - RemainingBold), RemainingBold).Font.Bold = True End If End Sub This illustration assumes an annual premium amount of $1,000.00 based on an annual payment mode. The values shown in the illustration assume that the premiums are paid at the beginning of the payment period. In addition, there is a single premium deposit of $999,999.99 on mm/dd/yyyy. The illustration shows total annual premiums assumed. Values will vary depending on the timing of premium payments. Employer contributions, if any, are not reflected in the illustration. This illustration assumes an annual premium amount of $1,000.00 based on an annual payment mode. The values shown in the illustration assume that the premiums are paid at the beginning of the payment period. In addition, this illustration assumes XX single premium deposits between mm/dd/yyyy and the assumed retirement date of mm/dd/yyyy. The illustration shows total annual premiums assumed. The amount and timing of these future single premium deposits are detailed on page 3 of this illustration. Values will vary depending on the timing of premium payments. Employer contributions, if any, are not reflected in the illustration. This illustration assumes an annual premium amount of $1,000.00 based on an annual payment mode. The values shown in the illustration assume that the premiums are paid at the beginning of the payment period. The illustration shows total annual premiums assumed. Values will vary depending on the timing of premium payments. Employer contributions, if any, are not reflected in the illustration. A single premium deposit of $999,999.99 on mm/dd/yyyy is assumed. Values will vary depending on the timing of premium payments. Employer contributions, if any, are not reflected in the illustration. Thiss illustration assumes XX single premium deposits between mm/dd/yyyy and the assumed retirement date of MM/DD/YYYY. The amount and timing of these future single premium deposits are detailed on page 3 of this illustration. Values will vary depending on the timing of premium payments. Employer contributions, if any, are not reflected in the illustration. "Mike H" wrote: Brad, Perhaps some sample data, the logical test to be applied and an example of the desired output may help in your quest for assistance. Mike "Brad" wrote: The project that I'm working has the following structure If item A is true - show sentence 1 If item B is true - show sentence 2 if item C is true - show sentence 3 . . . The list item is always true and show its sentence and make it bold (for this example lets assume that there are 45 characters is this sentence). I have the logic built to show only the "true" items. The "twist" is that these sentences need to be in paragraph form (therefore I have a macro that will "fill-justify these sentences into paragraph form and it works). Therefore, the last sentence could be in the last line or the last two lines. Is the best way to make sure the I bold the right text as follows: Start with the last row - count how many characters are in it. If it has more characters that the sentence that I'm concered about activecell.characters(2,45).font.bold = True (this assumes the last sentence is would start in position 2. If the last row as less characters than 45 characters bold the last row. - move up a row and bold the last (45-number of characters from row below) characters. I'm assuming that this will work or is there a better way to do this? Or am I not making sense? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bolding using VBA
I think this subroutine will bold the last sentence in a paragraph of
text... Sub BoldLastSentence(Cell As Range) Cell.Characters(InStrRev(Cell.Value, ". ") + 2).Font.Bold = True End Sub Just fill in the cell with your concatenated text and then pass the cell reference into the above subroutine. I'm thinking something like this... Sub YourMacro() .... .... <<Concatenate your text and put it into D7 for this example .... BoldLastSentence Range("D7") End Sub Or, of course, you could do away with the subroutine and just include the one line statement within your own code... Sub YourMacro() .... .... <<Concatenate your text and put it into D7 for this example .... Cell.Characters(InStrRev(Range("D7").Value, ". ") + 2).Font.Bold = True End Sub Rick "Brad" wrote in message ... Two things First of all Sub Bradstest1() Dim RemainingBold As Integer Dim SecondSentence As Integer shtPrem.Range("o12:o" & 12 + Range("d1").Value).Value = shtPrem.Range(Range("b1").Value).Value shtPrem.Range("o12:x22").Justify shtPrem.Range("o" & 12 + Range("d1").Value).Font.Bold = True RemainingBold = 70 - Len(shtPrem.Range("o" & 12 + Range("d1").Value)) SecondSentence = Len(shtPrem.Range("o" & 11 + Range("d1").Value)) If Len(shtPrem.Range("o" & 12 + Range("d1").Value)) < 70 Then shtPrem.Range("o" & 11 + Range("d1").Value).Characters(SecondSentence - RemainingBold, RemainingBold).Font.Bold = True End If End Sub works - I just have to make sure the each cell is not bolded before I start. But is there a better way to do this. Just remember that I'm going to have several different sentences that I'm going to put together (using Fill-Justity) and then bolding the last sentence........... "Brad" wrote: Listed below are 5 situations that could occur: These sentences have to be put into paragraph form. Also here is my VBA, it works excpet that it is bolding both the last and second to last sentences (after it is reformated) Please note regardless of where "Employer contributions, if any, are not reflected in the illustration." shows up it must be bolded. Sub Bradstest1() Dim RemainingBold As Integer Dim SecondSentence As Integer shtPrem.Range("o12:o" & 12 + Range("d1").Value).Value = shtPrem.Range(Range("b1").Value).Value shtPrem.Range("o12:x22").Justify shtPrem.Range("o" & 12 + Range("d1").Value).Font.Bold = True RemainingBold = 70 - Len(shtPrem.Range("o" & 12 + Range("d1").Value)) SecondSentence = Len(shtPrem.Range("o" & 11 + Range("d1").Value)) If Len(shtPrem.Range("o" & 12 + Range("d1").Value)) < 70 Then shtPrem.Range("o" & 11 + Range("d1").Value).Characters((SecondSentence - RemainingBold), RemainingBold).Font.Bold = True End If End Sub This illustration assumes an annual premium amount of $1,000.00 based on an annual payment mode. The values shown in the illustration assume that the premiums are paid at the beginning of the payment period. In addition, there is a single premium deposit of $999,999.99 on mm/dd/yyyy. The illustration shows total annual premiums assumed. Values will vary depending on the timing of premium payments. Employer contributions, if any, are not reflected in the illustration. This illustration assumes an annual premium amount of $1,000.00 based on an annual payment mode. The values shown in the illustration assume that the premiums are paid at the beginning of the payment period. In addition, this illustration assumes XX single premium deposits between mm/dd/yyyy and the assumed retirement date of mm/dd/yyyy. The illustration shows total annual premiums assumed. The amount and timing of these future single premium deposits are detailed on page 3 of this illustration. Values will vary depending on the timing of premium payments. Employer contributions, if any, are not reflected in the illustration. This illustration assumes an annual premium amount of $1,000.00 based on an annual payment mode. The values shown in the illustration assume that the premiums are paid at the beginning of the payment period. The illustration shows total annual premiums assumed. Values will vary depending on the timing of premium payments. Employer contributions, if any, are not reflected in the illustration. A single premium deposit of $999,999.99 on mm/dd/yyyy is assumed. Values will vary depending on the timing of premium payments. Employer contributions, if any, are not reflected in the illustration. Thiss illustration assumes XX single premium deposits between mm/dd/yyyy and the assumed retirement date of MM/DD/YYYY. The amount and timing of these future single premium deposits are detailed on page 3 of this illustration. Values will vary depending on the timing of premium payments. Employer contributions, if any, are not reflected in the illustration. "Mike H" wrote: Brad, Perhaps some sample data, the logical test to be applied and an example of the desired output may help in your quest for assistance. Mike "Brad" wrote: The project that I'm working has the following structure If item A is true - show sentence 1 If item B is true - show sentence 2 if item C is true - show sentence 3 . . . The list item is always true and show its sentence and make it bold (for this example lets assume that there are 45 characters is this sentence). I have the logic built to show only the "true" items. The "twist" is that these sentences need to be in paragraph form (therefore I have a macro that will "fill-justify these sentences into paragraph form and it works). Therefore, the last sentence could be in the last line or the last two lines. Is the best way to make sure the I bold the right text as follows: Start with the last row - count how many characters are in it. If it has more characters that the sentence that I'm concered about activecell.characters(2,45).font.bold = True (this assumes the last sentence is would start in position 2. If the last row as less characters than 45 characters bold the last row. - move up a row and bold the last (45-number of characters from row below) characters. I'm assuming that this will work or is there a better way to do this? Or am I not making sense? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bolding using VBA
I guess I should mention, the code will not work if you have an abbreviation
that ends with a period within your last sentence (for example, "My physician's name is Dr. John Smith"). To handle that situation, you would have to have a global variable and put the length of every entered sentence into it... when finished, it would contain the length of the last sentence and you could use the length of the text in the cell minus the length of this last sentence (plus one) to get the start value for the Characters property. Rick "Rick Rothstein (MVP - VB)" wrote in message ... I think this subroutine will bold the last sentence in a paragraph of text... Sub BoldLastSentence(Cell As Range) Cell.Characters(InStrRev(Cell.Value, ". ") + 2).Font.Bold = True End Sub Just fill in the cell with your concatenated text and then pass the cell reference into the above subroutine. I'm thinking something like this... Sub YourMacro() .... .... <<Concatenate your text and put it into D7 for this example .... BoldLastSentence Range("D7") End Sub Or, of course, you could do away with the subroutine and just include the one line statement within your own code... Sub YourMacro() .... .... <<Concatenate your text and put it into D7 for this example .... Cell.Characters(InStrRev(Range("D7").Value, ". ") + 2).Font.Bold = True End Sub Rick "Brad" wrote in message ... Two things First of all Sub Bradstest1() Dim RemainingBold As Integer Dim SecondSentence As Integer shtPrem.Range("o12:o" & 12 + Range("d1").Value).Value = shtPrem.Range(Range("b1").Value).Value shtPrem.Range("o12:x22").Justify shtPrem.Range("o" & 12 + Range("d1").Value).Font.Bold = True RemainingBold = 70 - Len(shtPrem.Range("o" & 12 + Range("d1").Value)) SecondSentence = Len(shtPrem.Range("o" & 11 + Range("d1").Value)) If Len(shtPrem.Range("o" & 12 + Range("d1").Value)) < 70 Then shtPrem.Range("o" & 11 + Range("d1").Value).Characters(SecondSentence - RemainingBold, RemainingBold).Font.Bold = True End If End Sub works - I just have to make sure the each cell is not bolded before I start. But is there a better way to do this. Just remember that I'm going to have several different sentences that I'm going to put together (using Fill-Justity) and then bolding the last sentence........... "Brad" wrote: Listed below are 5 situations that could occur: These sentences have to be put into paragraph form. Also here is my VBA, it works excpet that it is bolding both the last and second to last sentences (after it is reformated) Please note regardless of where "Employer contributions, if any, are not reflected in the illustration." shows up it must be bolded. Sub Bradstest1() Dim RemainingBold As Integer Dim SecondSentence As Integer shtPrem.Range("o12:o" & 12 + Range("d1").Value).Value = shtPrem.Range(Range("b1").Value).Value shtPrem.Range("o12:x22").Justify shtPrem.Range("o" & 12 + Range("d1").Value).Font.Bold = True RemainingBold = 70 - Len(shtPrem.Range("o" & 12 + Range("d1").Value)) SecondSentence = Len(shtPrem.Range("o" & 11 + Range("d1").Value)) If Len(shtPrem.Range("o" & 12 + Range("d1").Value)) < 70 Then shtPrem.Range("o" & 11 + Range("d1").Value).Characters((SecondSentence - RemainingBold), RemainingBold).Font.Bold = True End If End Sub This illustration assumes an annual premium amount of $1,000.00 based on an annual payment mode. The values shown in the illustration assume that the premiums are paid at the beginning of the payment period. In addition, there is a single premium deposit of $999,999.99 on mm/dd/yyyy. The illustration shows total annual premiums assumed. Values will vary depending on the timing of premium payments. Employer contributions, if any, are not reflected in the illustration. This illustration assumes an annual premium amount of $1,000.00 based on an annual payment mode. The values shown in the illustration assume that the premiums are paid at the beginning of the payment period. In addition, this illustration assumes XX single premium deposits between mm/dd/yyyy and the assumed retirement date of mm/dd/yyyy. The illustration shows total annual premiums assumed. The amount and timing of these future single premium deposits are detailed on page 3 of this illustration. Values will vary depending on the timing of premium payments. Employer contributions, if any, are not reflected in the illustration. This illustration assumes an annual premium amount of $1,000.00 based on an annual payment mode. The values shown in the illustration assume that the premiums are paid at the beginning of the payment period. The illustration shows total annual premiums assumed. Values will vary depending on the timing of premium payments. Employer contributions, if any, are not reflected in the illustration. A single premium deposit of $999,999.99 on mm/dd/yyyy is assumed. Values will vary depending on the timing of premium payments. Employer contributions, if any, are not reflected in the illustration. Thiss illustration assumes XX single premium deposits between mm/dd/yyyy and the assumed retirement date of MM/DD/YYYY. The amount and timing of these future single premium deposits are detailed on page 3 of this illustration. Values will vary depending on the timing of premium payments. Employer contributions, if any, are not reflected in the illustration. "Mike H" wrote: Brad, Perhaps some sample data, the logical test to be applied and an example of the desired output may help in your quest for assistance. Mike "Brad" wrote: The project that I'm working has the following structure If item A is true - show sentence 1 If item B is true - show sentence 2 if item C is true - show sentence 3 . . . The list item is always true and show its sentence and make it bold (for this example lets assume that there are 45 characters is this sentence). I have the logic built to show only the "true" items. The "twist" is that these sentences need to be in paragraph form (therefore I have a macro that will "fill-justify these sentences into paragraph form and it works). Therefore, the last sentence could be in the last line or the last two lines. Is the best way to make sure the I bold the right text as follows: Start with the last row - count how many characters are in it. If it has more characters that the sentence that I'm concered about activecell.characters(2,45).font.bold = True (this assumes the last sentence is would start in position 2. If the last row as less characters than 45 characters bold the last row. - move up a row and bold the last (45-number of characters from row below) characters. I'm assuming that this will work or is there a better way to do this? Or am I not making sense? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bolding using VBA
I like your idea (thanks), except for one fact....
If the characters in all sentences add up to more than (1024) characters then I can't get in one cell (which will be a situation that I will have). Is there a way around that issue? That is why I was using the ".justify" logic. But I'm not the best with VBA is there is a better way - I'm willing to learn..... "Rick Rothstein (MVP - VB)" wrote: I think this subroutine will bold the last sentence in a paragraph of text... Sub BoldLastSentence(Cell As Range) Cell.Characters(InStrRev(Cell.Value, ". ") + 2).Font.Bold = True End Sub Just fill in the cell with your concatenated text and then pass the cell reference into the above subroutine. I'm thinking something like this... Sub YourMacro() .... .... <<Concatenate your text and put it into D7 for this example .... BoldLastSentence Range("D7") End Sub Or, of course, you could do away with the subroutine and just include the one line statement within your own code... Sub YourMacro() .... .... <<Concatenate your text and put it into D7 for this example .... Cell.Characters(InStrRev(Range("D7").Value, ". ") + 2).Font.Bold = True End Sub Rick "Brad" wrote in message ... Two things First of all Sub Bradstest1() Dim RemainingBold As Integer Dim SecondSentence As Integer shtPrem.Range("o12:o" & 12 + Range("d1").Value).Value = shtPrem.Range(Range("b1").Value).Value shtPrem.Range("o12:x22").Justify shtPrem.Range("o" & 12 + Range("d1").Value).Font.Bold = True RemainingBold = 70 - Len(shtPrem.Range("o" & 12 + Range("d1").Value)) SecondSentence = Len(shtPrem.Range("o" & 11 + Range("d1").Value)) If Len(shtPrem.Range("o" & 12 + Range("d1").Value)) < 70 Then shtPrem.Range("o" & 11 + Range("d1").Value).Characters(SecondSentence - RemainingBold, RemainingBold).Font.Bold = True End If End Sub works - I just have to make sure the each cell is not bolded before I start. But is there a better way to do this. Just remember that I'm going to have several different sentences that I'm going to put together (using Fill-Justity) and then bolding the last sentence........... "Brad" wrote: Listed below are 5 situations that could occur: These sentences have to be put into paragraph form. Also here is my VBA, it works excpet that it is bolding both the last and second to last sentences (after it is reformated) Please note regardless of where "Employer contributions, if any, are not reflected in the illustration." shows up it must be bolded. Sub Bradstest1() Dim RemainingBold As Integer Dim SecondSentence As Integer shtPrem.Range("o12:o" & 12 + Range("d1").Value).Value = shtPrem.Range(Range("b1").Value).Value shtPrem.Range("o12:x22").Justify shtPrem.Range("o" & 12 + Range("d1").Value).Font.Bold = True RemainingBold = 70 - Len(shtPrem.Range("o" & 12 + Range("d1").Value)) SecondSentence = Len(shtPrem.Range("o" & 11 + Range("d1").Value)) If Len(shtPrem.Range("o" & 12 + Range("d1").Value)) < 70 Then shtPrem.Range("o" & 11 + Range("d1").Value).Characters((SecondSentence - RemainingBold), RemainingBold).Font.Bold = True End If End Sub This illustration assumes an annual premium amount of $1,000.00 based on an annual payment mode. The values shown in the illustration assume that the premiums are paid at the beginning of the payment period. In addition, there is a single premium deposit of $999,999.99 on mm/dd/yyyy. The illustration shows total annual premiums assumed. Values will vary depending on the timing of premium payments. Employer contributions, if any, are not reflected in the illustration. This illustration assumes an annual premium amount of $1,000.00 based on an annual payment mode. The values shown in the illustration assume that the premiums are paid at the beginning of the payment period. In addition, this illustration assumes XX single premium deposits between mm/dd/yyyy and the assumed retirement date of mm/dd/yyyy. The illustration shows total annual premiums assumed. The amount and timing of these future single premium deposits are detailed on page 3 of this illustration. Values will vary depending on the timing of premium payments. Employer contributions, if any, are not reflected in the illustration. This illustration assumes an annual premium amount of $1,000.00 based on an annual payment mode. The values shown in the illustration assume that the premiums are paid at the beginning of the payment period. The illustration shows total annual premiums assumed. Values will vary depending on the timing of premium payments. Employer contributions, if any, are not reflected in the illustration. A single premium deposit of $999,999.99 on mm/dd/yyyy is assumed. Values will vary depending on the timing of premium payments. Employer contributions, if any, are not reflected in the illustration. Thiss illustration assumes XX single premium deposits between mm/dd/yyyy and the assumed retirement date of MM/DD/YYYY. The amount and timing of these future single premium deposits are detailed on page 3 of this illustration. Values will vary depending on the timing of premium payments. Employer contributions, if any, are not reflected in the illustration. "Mike H" wrote: Brad, Perhaps some sample data, the logical test to be applied and an example of the desired output may help in your quest for assistance. Mike "Brad" wrote: The project that I'm working has the following structure If item A is true - show sentence 1 If item B is true - show sentence 2 if item C is true - show sentence 3 . . . The list item is always true and show its sentence and make it bold (for this example lets assume that there are 45 characters is this sentence). I have the logic built to show only the "true" items. The "twist" is that these sentences need to be in paragraph form (therefore I have a macro that will "fill-justify these sentences into paragraph form and it works). Therefore, the last sentence could be in the last line or the last two lines. Is the best way to make sure the I bold the right text as follows: Start with the last row - count how many characters are in it. If it has more characters that the sentence that I'm concered about activecell.characters(2,45).font.bold = True (this assumes the last sentence is would start in position 2. If the last row as less characters than 45 characters bold the last row. - move up a row and bold the last (45-number of characters from row below) characters. I'm assuming that this will work or is there a better way to do this? Or am I not making sense? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help with formula for bolding font | Excel Worksheet Functions | |||
Macro Help - Bolding info | Excel Programming | |||
Bolding Row from VB6 | Excel Programming | |||
Bolding when subtotalling | Excel Worksheet Functions | |||
Bolding | Excel Programming |