ActiveX Textbox question

S

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

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

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top