Need help converting macro from 2002 to 2007

V

vbaexperimenter

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(msoTextOrientationHorizontal, 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
 
J

Jim Rech

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
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(msoTextOrientationHorizontal, 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
 
V

vbaexperimenter

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

Jim Rech

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(msoTextOrientationHorizontal, 100, 100, 0,
40).Select

--
Jim
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
| > 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(msoTextOrientationHorizontal,
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
| >
| >
| >
 
V

vbaexperimenter

Thanks for your help that worked.

Jim Rech said:
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(msoTextOrientationHorizontal, 100, 100, 0,
40).Select

--
Jim
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
| > 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(msoTextOrientationHorizontal,
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
| >
| >
| >
Click to expand...
 

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

Similar Threads

Annotating charts with text from within VBA 11
Adding Text Boxes to New Page 4
Put a picture in a text box without selecting it? 2
Text Overflow 4
Adding shape-type textbox into excel chart sheet 2
VBA for green empty comment 1
Insert Pic Which User Chooses into Comment 1
Hide/Show Shapes on Worksheet?? 2

Top