No automatic Wrap Text in Cell referencing another cell

mimu

New Member
Joined
Apr 29, 2003
Messages
9
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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Suppose the typed text is in cell A1 on Sheet1 and the formula is in cell A1 on Sheet2. Right click the tab for Sheet1 and choose View Code. Paste this code into the window on the right:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$A$1" Then Exit Sub
    Worksheets("Sheet2").Range("A1").WrapText = True
End Sub

Press Alt+F11 to return to your worksheet. Now when you change the entry on Sheet1 the row height on Sheet2 will be adjusted automatically.
 
Upvote 0
Thanks Andrew for the quick answer. I have it working now, and I tried to
adapt your code to make it applicable for all cells in Sheet2, but I didn't
succeed. In fact, I would like this to be a kind of defualt for
all cells that have references to other cells. So, all cells in Sheet2 that
have a reference to any cell in Sheet1, or to a cell in Sheetn, should have
this behaviour.
How does the code than look like??

Thanks, Miriam


Andrew Poulsom said:
Suppose the typed text is in cell A1 on Sheet1 and the formula is in cell A1 on Sheet2. Right click the tab for Sheet1 and choose View Code. Paste this code into the window on the right:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$A$1" Then Exit Sub
    Worksheets("Sheet2").Range("A1").WrapText = True
End Sub

Press Alt+F11 to return to your worksheet. Now when you change the entry on Sheet1 the row height on Sheet2 will be adjusted automatically.
 
Upvote 0
Can we narrow it down a bit? Are the cells in Sheet1 and Sheet2 in a particular range (or column)?

The reason I ask it that running the code would slow down your workbook if it's not specific.
 
Upvote 0
ok then, the situation is as follows:
workbook has 13 sheets, OVERVIEW, AUG, SEPT,OCT,....JUN,JUL
Sheets AUG .. JUL each have a column with PUPIL1 in cell A1
PUPIL 2 in cell A2
etc.
For each PUPIL, per month, the teacher enters a comment in
B1 for PUPIL1
B2 for PUPIL2,
etc.
The comment is a wrap text cell, and it works fine.

The OVERVIEW sheet has all collumns for the whole year
collected for each PUPIL.
EG: PUPIL1 in A1 ; reference to AUG!B1 in B1 (so, reference the comments)
reference to SEPT!B1 in B2
reference to OCT!B1 in B3
etc

PUPIL2 in A13 ; reference to AUG!B2 in B13
reference to SEPT!B2 in B14
etc.

So, it is in this OVERVIEW sheet that I want the text to wrap
around just as in all the 12 sheets for each month!

Hope you can help me out, Miriam


Andrew Poulsom said:
Can we narrow it down a bit? Are the cells in Sheet1 and Sheet2 in a particular range (or column)?

The reason I ask it that running the code would slow down your workbook if it's not specific.
 
Upvote 0
You might get away with this code in the module for the OVERVIEW worksheet:

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

If your workbook becomes too slow post back.
 
Upvote 0
Hi Andrew,

I tried it and it works! (Although, I'm still wondering why I have to go
through all this code to have something that everyone expects to
be a default????)

One other question: I assume this code is language
independent, i.e. it also works in the Dutch version of Excel??

Thanks again for your quick and accurate support! Miriam


quote="Andrew Poulsom"]You might get away with this code in the module for the OVERVIEW worksheet:

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

If your workbook becomes too slow post back.[/quote]
 
Upvote 0
Hi Andrew,

I thought everything worked fine, but not anymore!
I closed a correct working workbook, including the code for Wrap Text,
today I opened it again, and it doesn't work anymore, i.e.
it doesnt wrap the text in a cell containing a reference to another cell.

Any idea what's going on? Should I somehow explicitly acitivate
the code??

Regards, Miriam


mimu said:
Hi Andrew,

I tried it and it works! (Although, I'm still wondering why I have to go
through all this code to have something that everyone expects to
be a default????)

One other question: I assume this code is language
independent, i.e. it also works in the Dutch version of Excel??

Thanks again for your quick and accurate support! Miriam


quote="Andrew Poulsom"]You might get away with this code in the module for the OVERVIEW worksheet:

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

If your workbook becomes too slow post back.
[/quote]
 
Upvote 0
Hi Andrew,

I run into the following problem:
it looks like if the code does not work on Protected cheets...
The Sheet called OVERVIEW is protected, and then the WrapText
cannot be set on. Is that corrct? How can I go around this?

Regards, Miraim


Andrew Poulsom said:
You might get away with this code in the module for the OVERVIEW worksheet:

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

If your workbook becomes too slow post back.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top