Excel 2003/2007autosize textbox width to match cell width

[grady121]

I have a code that is used to enter a 'Single line' Textbox onto the worksheet, which works great in XL2003. But not in XL2007.

The end user could be using either XL2003 or 2007, so I was hoping that the width of the new Textbox would be set by the column width, (which is set to Autofit) but in XL2007, I end up with shortened textbox with the text on two lines?

Code:

MyText = InputBox("Enter text", "Single Line Text")

Set Cell = ActiveSheet.Range("BZ1")
Cell.Value = MyText
Columns("BZ:BZ").EntireColumn.AutoFit
Rows("1:1").EntireRow.AutoFit


ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 40, 40, 40, 40).Select 'Create a Textbox

With Selection

.Left = Cell.Left
.TOP = Cell.TOP
.Height = Cell.Height
.Width = Cell.Width

.Characters.Text = Cell.Value
.Border.Color = vbBlack
.Font.Color = vbBlack
.Font.Name = "Arial"
.Font.FontStyle = "Regular"

End With


Can anyone offer any advice to a Newbie.

Thanks in advance.

 

[Domenic]

For Excel 2010, the textbox has an internal margin. So it's likely that 2007 also has an internal margin, whereas 2003 probably does not. If this is the case, you'll need to include code to check which version of Excel is being used, and then set the internal margin for left and right to 0 when the version being used is 2007 (or later). Or, alternatively, I guess you could probably replace...

.Width = Cell.Width

with

.Width = Cell.Width * 1.1