Need help converting macro from 2002 to 2007
Thanks for your help that worked.
"Jim Rech" wrote:
The "text box" you get with this macro in Excel 2007 is different than the
one in earlier versions as MS decided to conform "shapes" used across Office
applications. We lost the traditional text box and it was replaced with a
rectangle. It appears that setting a shape to autosize with its the text it
contains affects only the height of this shape not the width. The width
stays at whatever the macro set it at, which happens to be zero in your
case. You'd at least want to set it to something wider but how you can make
it the exact width for the text I don't know.
ActiveSheet.Shapes.AddTextbox(msoTextOrientationHo rizontal, 100, 100, 0,
40).Select
--
Jim
"vbaexperimenter" wrote in
message ...
| Thanks Jim that worked. One other question, when the macro was executed in
| Office 2002 it would resize automatically. In 2007 it isn't, any thoughts
on
| that?
|
| "Jim Rech" wrote:
|
| Your code runs for me in Excel 2007. If you have Option Explicit at the
top
| of the module add:
|
| Dim strWP As String
|
| as the second line after Sub Work_Paper()
|
| --
| Jim
| "vbaexperimenter" wrote in
| message ...
| | This is a macro that one of our ex-employees created in excel 2002.
When
| I
| | try to run it in 2007 I get the error Variable not defined on the line
2nd
| | line strWP. I can't find anything online to help me to replace that
| command.
| | I found that it is a Varient string, but nothing else. Can someone
give me
| | some ideas?
| |
| | Sub Work_Paper()
| | '
| | strWP = InputBox("Enter workpaper reference:", "Workpaper")
| | ActiveSheet.Shapes.AddTextbox(msoTextOrientationHo rizontal,
100,
| | 100, 0# _
| | , 0#).Select
| | With Selection.Font
| | .Name = "Arial"
| | .FontStyle = "Bold"
| | .Size = 8
| | .Strikethrough = False
| | .Superscript = False
| | .Subscript = False
| | .OutlineFont = False
| | .Shadow = False
| | .Underline = xlUnderlineStyleNone
| | .ColorIndex = 2
| | End With
| | Selection.ShapeRange.Fill.Visible = msoTrue
| | Selection.ShapeRange.Fill.Solid
| | Selection.ShapeRange.Fill.ForeColor.SchemeColor = 12
| | Selection.ShapeRange.Fill.Transparency = 0#
| | Selection.ShapeRange.Line.Weight = 0.75
| | Selection.ShapeRange.Line.DashStyle = msoLineSolid
| | Selection.ShapeRange.Line.Style = msoLineSingle
| | Selection.ShapeRange.Line.Transparency = 0#
| | Selection.ShapeRange.Line.Visible = msoTrue
| | Selection.ShapeRange.Line.ForeColor.SchemeColor = 12
| | Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
| | Selection.ShapeRange.TextFrame.MarginLeft = 0.75
| | Selection.ShapeRange.TextFrame.MarginRight = 0.75
| | Selection.ShapeRange.TextFrame.MarginTop = 0#
| | Selection.ShapeRange.TextFrame.MarginBottom = 0#
| | Selection.ShapeRange(1).TextFrame.AutoSize = msoTrue
| | Selection.Characters.Text = strWP
| | Selection.ShapeRange.Height = 10.2
| | Selection.Cut
| | ActiveSheet.Paste
| | End Sub
|
|
|
|