determine if wrap text is set within a range

[Guest]

is it possible to determine if a cell or multiple cells within a range
have the wrap text property set to true? And then to get the addresses
of those cells?

tia,
dk

 

[Helmut Weber]

Hi dk

like this:

Dim oCll As Range
For Each oCll In Selection.Cells
If oCll.WrapText = True Then
MsgBox oCll.Address
End If
Next

--
Greetings from Bavaria, Germany

I'm not an Excel-expert.
Just exploring strange territory.

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Guest]

Thanks

So it appears that Looping through the range is the only way?

 

[Helmut Weber]

Hi,

on MsgBox Selection.Cells.WrapText

error 94 occurs,

if not all cells have the same wrap property,
which could speed up checking.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Dave Peterson]

Depends on how much info you need.

If you select a range and look at format|cells|alignment tab, you'll see that
the wraptext is either:

clear (none has that setting)
checked (green check) (all have that setting)
or light check (green block) (some do, some don't).

You can check for the same thing in code:

Option Explicit
Sub testme01()
Dim myWT As Variant
Dim myRng As Range
Dim myCell As Range
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
Set myRng = .Range("a1:c10")
End With

myWT = myRng.WrapText

If myWT = True Then
'all cells have that setting
MsgBox myRng.Address(0, 0) & " are all set for wrap text"
ElseIf myWT = False Then
'no cells have that setting
MsgBox myRng.Address(0, 0) & " is not set for wrap text"
Else
'mywt = null
For Each myCell In myRng.Cells
If myCell.WrapText = True Then
MsgBox myCell.Address(0, 0) & " is set for wrap text"
End If
Next myCell
End If

End Sub