About Us

vbaexperimenter

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Excel 2002 to 2007 - issues with Macro / VB?	Don	Excel Discussion (Misc queries)	6	February 18th 09 10:34 PM
Converting Excel 2002 to MS Works	T. Jenkins	Excel Discussion (Misc queries)	4	November 8th 08 10:43 PM
Macro Security Excel 2002 vs. 2007?	Dan the Man[_2_]	Excel Discussion (Misc queries)	0	August 24th 08 05:54 PM
converting 2002 workbooks to 2007	MJY	Excel Discussion (Misc queries)	1	January 27th 08 08:41 PM
Converting Lotus 123 Release 9 to Excel 2002	Jim Rech	Excel Programming	1	August 6th 04 06:25 PM

Menu

About Us