No automatic Wrap Text in Cell referencing another cell

[mimu]

I have a sheet with a cell (say A1) with multiple text lines, and this cell has
Wrap text set to ON (in Format Cells, Alligment). No problem here, the
text nicely wraps around as I enter more text.

However, I have another sheet that has to show the SAME text, and the
SAME format
as entered in A1 in the previouw sheet. When I enter a reference
to A1 in the other sheet, the text is shown, but it does NOT wrap around, although the Wrap text is set to ON here as well!!

Only when I set Wrap text manually OFF and ON again, the text
wraps around as expected. But I cannot expect the users of my sheet
to do this!! Closing and opening the file has the same effect, but this is
also not what one wants...

What must I do to get the Wrap text immediately executed in the cell that
has the reference to the other cell. To be very clear on this: both cells have
Wrap text set to ON!

Cheers, MIMU

 

[Andrew Poulsom]

Try this:

Private Sub Worksheet_Calculate() 
    With Worksheets("OVERVIEW") 
        .Unprotect "PasswordGoesHere"
        .Range("B1:B" & .Range("B1").End(xlDown).Row).WrapText = True 
        .Protect "PasswordGoesHere"
    End With 
End Sub

Change PasswordGoesHere to the actual password, or omit it if there isn't one.

 

[Richie(UK)]

Deja vu :

http://www.mrexcel.com/board2/viewtopic.php?t=49808&highlight=

Miriam - please keep related questions all on the same thread. Thanks :wink:

 

[mimu]

Hi there,

I'm really lost now. Please help me out.
I use the same code in one workbook and it works fine,
but I use it in another and it doesn't work. Both workbooks
have NO protections and macros are enabled (security is low).
I have NO clue anymore wy it doesn't work, i.e. the sheet
does NOT wrap the text in collumn B.

The code I use is:
------------------------------------------------------
Private Sub Worksheet_Calculate()
With Worksheets("OVERVIEW")
.Range("B1:B" & .Range("B1").End(xlDown).Row).WrapText = True
End With
End Sub
--------------------------------------------------------

Is there I way that I can send you the workbook that
you can see exactly what's going on?

 

[Andrew Poulsom]

Maybe it's because calculation is set to manual in the workbook which doesn't behave as expected.

 

[mimu]

I checked this, but calculation was set to automatic, so
that wasn't the cause; however, meanwhile I found out
what it was (I think), and now it works, so I'm happy again!
The problem was that the range was from B1 downwards,
but B1..B3 where some buttons for executing macros.
I changed B1 to B6 downwards (normal text fields) and then it
worked!! Thanks a lot for your help!

Miriam

Maybe it's because calculation is set to manual in the workbook which doesn't behave as expected.