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

[Greg Wilson]

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" wrote:

> 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
>
>
>

[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 :D4, 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

"Greg Wilson" <[email protected]> wrote in message
news:[email protected]...
>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" wrote:
>
>> 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
>>
>>
>>

[Greg Wilson]

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" wrote:

> 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 :D4, 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
>
> "Greg Wilson" <[email protected]> wrote in message
> news:[email protected]...
> >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" wrote:
> >
> >> 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
> >>
> >>
> >>
>
>
>

[Colin Telfer]

Thnaks Greg its worked a treat

Regards

Colin

"Greg Wilson" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
>> 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 :D4, 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
>>
>> "Greg Wilson" <[email protected]> wrote in message
>> news:[email protected]...
>> >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" wrote:
>> >
>> >> 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
>> >>
>> >>
>> >>
>>
>>
>>