Text wrapping on merged cell

[Colin Telfer]

Does anyone know how to get the text to wrap on a range of merged cells
which adjusts with the row height, i have managed it with a single cell,
but when merged cells are used it doesn't work. is there a workaround?

Thanks in advance

Colin

 

[Guest]

I have an adaption from Jim Rech's original code that automates this using
the ws_change event:

http://tinyurl.com/n59er

Alternatively, you can set the column width of a single cell in the same row
as each merged range the same as the combined column widths of the merged
range. Insert a formula that references the first cell of the merged range
(e.g. "=A1"). Therefore, the text in this cell will be exactly the same as
the merged range. Set wraptext to True. Format it exactly the same except
have its font colour the same as the cell's interior colour in order to hide
the text. The cell probably should also be offscreen.

Then use the ws_change event to force autofit of the cell. The merged range
will also autofit since it is in the same row.

Regards,
Greg

 

[Colin Telfer]

Greg

Thanks for replying, I am a novice to VBA, and not quite sure what to do
with the code you refer to, I have a spreadsheet with a range of cells
merged, A4 4, which as I would enter the title form a form with a text box
the value of the text box is entered into the cell which I have managed to
do, but I want the formatting in the range of merged cells to adjust the row
hieght accordingly to make it fit and text wrap as well.

ring3 = TextBox3.Value

ActiveSheet.Range("A4").Select

ActiveCell.Value = ring3

'Worksheets("Sheet1").Range("A4").WrapText = True

Above is the code used, as you can see I have made the normal cell
formatting a comment as it does not work for merged cells.

Much appreciate any help / guidance you can give

Regards

Colin

 

[Guest]

Paste the code to the worksheet's class module - i.e. Sheet1(Sheet1) in the
list of Microsoft Excel Objects available through the VBE's Project Explorer
window. The code will then fire automatically in response to change made to
cell contents (worksheet_change event).

You don't need to tell it to wrap the text. The Wraptext property of the
merged range needs to be set though: Format > Cells > Alignment tab > "Wrap
text" checkbox. This need only be done once and is probably already set.

I am assuming that the code you appended is in a Userform code module. It is
possible that earlier versions of Excel (pre xl2000) won't fire the ws_change
event in response to programmatic change made to cell contents in case you
are running xl97 or earlier.

I simplified your code. Note that it is seldom necessary to programmatically
select anything. Just refer to it directly in your code.

Dim ring3 As String
ring3 = TextBox1.Value
ActiveSheet.Range("A4").Value = ring3

Regards,
Greg

 

[Colin Telfer]

Thnaks Greg its worked a treat

Regards

Colin

Paste the code to the worksheet's class module - i.e. Sheet1(Sheet1) in
the
list of Microsoft Excel Objects available through the VBE's Project
Explorer
window. The code will then fire automatically in response to change made
to
cell contents (worksheet_change event).

You don't need to tell it to wrap the text. The Wraptext property of the
merged range needs to be set though: Format > Cells > Alignment tab >
"Wrap
text" checkbox. This need only be done once and is probably already set.

I am assuming that the code you appended is in a Userform code module. It
is
possible that earlier versions of Excel (pre xl2000) won't fire the
ws_change
event in response to programmatic change made to cell contents in case you
are running xl97 or earlier.

I simplified your code. Note that it is seldom necessary to
programmatically
select anything. Just refer to it directly in your code.

Dim ring3 As String
ring3 = TextBox1.Value
ActiveSheet.Range("A4").Value = ring3

Regards,
Greg