Bolding using VBA

[Brad]

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?

 

[Mike H]

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]

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" & 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.

 

[Brad]

Two things

First of all

Sub Bradstest1()
Dim RemainingBold As Integer
Dim SecondSentence As Integer

shtPrem.Range("o12" & 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]

I think that this is better


Sub ReformatSentences()
Dim RemainingBold As Integer
Dim SecondSentence As Integer
Dim LastSentence As Integer

shtPrem.Range("o12" & 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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Brad]

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.....