Solved: Wrapping text question

[malik641]

I have a drop down list in cell A2. Cell B2 has a formula that will choose another cell based on A2. The formula is:

=IF(A2=$K$2,$L$2,IF(A2=$K$3,$L$5,IF(A2=$K$4,$L$8,IF(A2=$K$5,$L$11,""))))

The problem is when I choose a value for A2, the formula works fine, but there is too much text for the cell. I formatted the cells for text wrapping, but it doesn't work with the formula (unless A2 has a value and I click on the formula in B2 and press F9 then Enter...but that's inefficient). Can I make a macro to adjust the row for the correct size to fit all the text, based on this information??

Thanks in advance!

[johnske]

You mean something like this maybe?

Option Explicit
Private Sub Worksheet_Activate()
      Cells.Columns.AutoFit
End Sub

HTH,
John

[MWE]

I have a drop down list in cell A2. Cell B2 has a formula that will choose another cell based on A2. The formula is:

=IF(A2=$K$2,$L$2,IF(A2=$K$3,$L$5,IF(A2=$K$4,$L$8,IF(A2=$K$5,$L$11,"")))) 

The problem is when I choose a value for A2, the formula works fine, but there is too much text for the cell. I formatted the cells for text wrapping, but it doesn't work with the formula (unless A2 has a value and I click on the formula in B2 and press F9 then Enter...but that's inefficient). Can I make a macro to adjust the row for the correct size to fit all the text, based on this information??

Thanks in advance!

I think you are asking two questions:
1. can a macro set "word wrap"?
2. can a macro adjust the width of the column to accomodate the text?

The answer to both is yes. For the first, you might try something like:

Sub WordWrap()
Range("A2").Select
    With Selection
        .VerticalAlignment = xlCenter
        .WrapText = True
    End With
End Sub

I use a centered vertical alignment whenever I wrap text (looks better to me), but you may not wish to do that.

For the second, the width of the column will depend on the amount of text and the particular font & font size being used. If there is more than a dozen or so characters, you should use a combination of col width and word wrap. An example of setting col width based on text found in cell A2 might look like:

Sub ColWidth()
    Columns("A:A").ColumnWidth = Len(Range("A2").Text) / 5
End Sub

For the font and font size I was using (Arial 10), setting the width to # chars / 5 seemed like a good compromise.

I assume you know how to convert your cascaded IF statement to VBA and how to combine the ideas above. Also, remember that setting the col width impacts every cell in that col.

[malik641]

Thanks to you both MWE and johnske!
I should have specified a little further that I wanted both the column width and the row height adjusted. But I figured it out pretty easily. I had a worksheet change event to border the row which was:

Private Sub Worksheet_Change(ByVal Target As Range)
     'Borders the previous row if a value is entered in the A:A column
    Dim LastCol As Long
    If Target.Column = 1 Then
    LastCol = Range("IV" & 1).End(xlToLeft).Column - 1
    LastCol = IIf(LastCol < 0, 0, LastCol)
    If Target.Value = Empty Then
        Range(Target.Offset(0, 0), Target.Offset(0, LastCol)) _
        .Borders.LineStyle = xlNone
    Else
        Range(Target.Offset(0, 0), Target.Offset(0, LastCol)) _
        .Borders.Weight = xlThin
    End If
    End If
Cells.Columns.AutoFit
Cells.Rows.AutoFit
End Sub

And I added the two bolded items and it works great!!! Mark it solved!

[malik641]

....A little modification is needed...
This works oookay...but what I want is a little different.
Check out my example. Put zoom on 100% (preferrably in 1024 X 768) and select one of the choices in A1. Notice how the text in column C is meant to have Breaks in the text for better viewing, but the autofit only autofits to the first row (1:1). It worked fine at 65%, but that was because of the text in row 1:1.

I want to have the column width formatted for whichever must be wider (either the target cell or the first row) to format the cell to be as wide as needed for both the target row and the first row.

Take a look (and Thanks in advance):