Thnaks Greg its worked a treat

Regards

Colin

"Greg Wilson" wrote in message
...
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" wrote in message
...
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