ActiveX Textbox question

[Stuart]

I create an ActiceX Textbox, with wraptext, Tab key and
Enter key enabled.
When I enter text data, the layout in the textbox is fine.
However when I delete the box it all goes haywire...........
that's to say the first cell's rowheight increases to match
the overall 'height' of the textbox, and all the data is in one
column.

Can I preserve the data layout, when the box is deleted
please?
In addition, there are little square 'box' characters amongst
the data. Why is this please?

Here's the kindly given delete code:

With ActiveWorkbook.Sheets("Contract Master Order")
.Unprotect Password:="SGB"
.Cells.Locked = False
Dim oleObj As OLEObject
Dim rng As Range
For Each oleObj In ActiveWorkbook.Sheets _
("Contract Master Order").OLEObjects
If TypeOf oleObj.Object Is MSForms.TextBox Then
Set rng = oleObj.TopLeftCell
rng.Value = oleObj.Object.Value
oleObj.Delete
End If
Next
End With

Regards.

 

[Tom Ogilvy]

With ActiveWorkbook.Sheets("Contract Master Order")
.Unprotect Password:="SGB"
.Cells.Locked = False
Dim oleObj As OLEObject
Dim rng As Range
For Each oleObj In ActiveWorkbook.Sheets _
("Contract Master Order").OLEObjects
If TypeOf oleObj.Object Is MSForms.TextBox Then
Set rng = oleObj.TopLeftCell
rng.Value = application.Substitute( _
oleObj.Object.Value,chr(13),"")
oleObj.Delete
End If
Next
End With


If the cell width approximates the textbox width and the height the height
and wordwrap is on, then you should get about the same result.

 

[Stuart]

Many thanks.

If the textbox has to 'match' the cell (height, width etc)
then I'm looking for some other way, I think.

Regards.

 

[Tom Ogilvy]

To me, you don't make any sense. If you want the cell to look the same as
the textbox, then how is the text going to wrap the same if the height and
width aren't the same? Don't bother to answer since your working with
Norman.