ExcelBanter - Need help converting macro from 2002 to 2007

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Need help converting macro from 2002 to 2007 (https://www.excelbanter.com/excel-programming/413057-need-help-converting-macro-2002-2007-a.html)

Need help converting macro from 2002 to 2007

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

Need help converting macro from 2002 to 2007

Need help converting macro from 2002 to 2007

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

Need help converting macro from 2002 to 2007

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

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