S
SDuguay
This macro code works fine to draw an ellipse in a black cell, as long
as the macro is in the same workbook as the active sheet. But, when I
try running from a sheet in a diffferent workbook, it fails with this
message.
"Runtime error: 1004. The specified value is out of range"
Any ideas how I can fix this. It fails at the AddShape Line.
Macro code is below:
Sub nlDrawEllipseInBlackCell()
Dim iTop As Integer ' top of shape
Dim iLeft As Integer ' left edge of shape
Dim iWidth As Integer ' width of shape
Dim iHeight As Integer ' width of shape
' Remember the position, height and width of the selected cell.
iTop = Selection.Top + 1
iLeft = Selection.Left + 2
iWidth = Selection.Width - 4
iHeight = Selection.Height - 3
'Draw the ellipse
ActiveSheet.Shapes.AddShape(msoShapeOval, 365.25, 204.75, 72#,
72#).Select
Selection.ShapeRange.Fill.Visible = msoFalse
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 = 9
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
Selection.ShapeRange.LockAspectRatio = msoFalse
Selection.ShapeRange.Height = iHeight ' eg. 11.25
Selection.ShapeRange.Width = iWidth ' eg. 58.5
Selection.ShapeRange.Rotation = 0#
'Move it to the cell
ActiveSheet.Shapes(ActiveSheet.Shapes.Count).Top = iTop
ActiveSheet.Shapes(ActiveSheet.Shapes.Count).Left = iLeft
End Sub
as the macro is in the same workbook as the active sheet. But, when I
try running from a sheet in a diffferent workbook, it fails with this
message.
"Runtime error: 1004. The specified value is out of range"
Any ideas how I can fix this. It fails at the AddShape Line.
Macro code is below:
Sub nlDrawEllipseInBlackCell()
Dim iTop As Integer ' top of shape
Dim iLeft As Integer ' left edge of shape
Dim iWidth As Integer ' width of shape
Dim iHeight As Integer ' width of shape
' Remember the position, height and width of the selected cell.
iTop = Selection.Top + 1
iLeft = Selection.Left + 2
iWidth = Selection.Width - 4
iHeight = Selection.Height - 3
'Draw the ellipse
ActiveSheet.Shapes.AddShape(msoShapeOval, 365.25, 204.75, 72#,
72#).Select
Selection.ShapeRange.Fill.Visible = msoFalse
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 = 9
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
Selection.ShapeRange.LockAspectRatio = msoFalse
Selection.ShapeRange.Height = iHeight ' eg. 11.25
Selection.ShapeRange.Width = iWidth ' eg. 58.5
Selection.ShapeRange.Rotation = 0#
'Move it to the cell
ActiveSheet.Shapes(ActiveSheet.Shapes.Count).Top = iTop
ActiveSheet.Shapes(ActiveSheet.Shapes.Count).Left = iLeft
End Sub