Comments added and formatted by VBA...

[jag108]

Hi Guys...

I have recorded a macro using Excel 2003 on XP SP2, but the code will not run. I get a stop/debug error when the code reaches the "Selection.ShapeRange" lines of code, with the error "Runtime error 438 - Object does not support this property or method."

Offending code:

Range("F49").Select
Range("F49").AddComment
Range("F49").Comment.Visible = False
Range("F49").Comment.Text Text:="Path:" & Chr(10) & "" & Chr(10) & "Menu Right:"
With Selection.Font
.Name = "Tahoma"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.ReadingOrder = xlContext
.Orientation = xlHorizontal
'.AutoSize = True
End With
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(87, 103, 173)
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 1#
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.RGB = RGB(0, 0, 0)
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
Selection.ShapeRange.LockAspectRatio = msoTrue
Selection.ShapeRange.Height = 55.5
Selection.ShapeRange.Width = 96#
With Selection
.Locked = False
.LockedText = False
End With
With Selection
.Placement = xlMove
.PrintObject = True
End With
Selection.ShapeRange.AlternativeText = ""
Range("F49").Select
End Sub

Thanks for any help offered.

 

[Nimrod]

Hello jag108
... what is the objective of the code ?
... does the activesheet have the object you attempting to select ?

 

[jag108]

Hi Nimrod,

I have an application that I am looking after that has 700 user rights to menus and reports. To adminster this I need to put various information in cells as well as the comment, so that I can visually see which user has which right.

I have all of the data required in a sheet in the same workbook, but not as I require and and of course not where I require.

I will adjust the code to select the data (sheet(s), cell etc..) later I just need to get the error ironed out first.

Thanks for the fast response.