Thnaks Greg its worked a treat

Regards

Colin

"Greg Wilson" <GregWilson@discussions.microsoft.com> wrote in message
news:707CA8EF-5A0E-423D-BAF0-82F82F6DE0A0@microsoft.com...
> 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" <GregWilson@discussions.microsoft.com> wrote in message
>> news:584DCCD7-CD53-4709-BF9D-D14939BF955A@microsoft.com...
>> >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
>> >>
>> >>
>> >>
>>
>>
>>