primaryteach
01-08-2010, 05:18 AM
Hi everyone and Happy New Year.
I would like to be able to add any picture to any cell in a comment box (as a fill). I used the macro recorder which generated the code below.
However, it has two problems:
1) It refers to a specific file on my hard drive. How do I change the code so that I can choose which picture I want each time?
2) The macro recorder has hard-coded the cell reference for the comment. Is there a way to change the code so that when the macro runs, it adds the comment to the cell that is currently selected?
I will need my hand held through this, as my VBA knowledge is very poor!
Thanks,
Simon
Sub addpicture()
' addpicture Macro
Range("I18").Select
Range("I18").AddComment
Range("I18").Comment.Visible = False
Range("I18").Comment.Text Text:="" & Chr(10) & ""
Range("I18").Select
ActiveCell.Comment.Visible = True
Range("I18").Comment.Shape.Select True
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.RGB = RGB(0, 0, 0)
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 255, 255)
Selection.ShapeRange.Fill.BackColor.RGB = RGB(255, 255, 225)
Selection.ShapeRange.Fill.UserPicture _
"C:\Users\Simon\Pictures\Building blocks2.gif"
Selection.ShapeRange.LockAspectRatio = msoFalse
Selection.ShapeRange.Height = 127.5
Selection.ShapeRange.Width = 283.5
Range("I18").Select
ActiveCell.Comment.Visible = False
End Sub
I would like to be able to add any picture to any cell in a comment box (as a fill). I used the macro recorder which generated the code below.
However, it has two problems:
1) It refers to a specific file on my hard drive. How do I change the code so that I can choose which picture I want each time?
2) The macro recorder has hard-coded the cell reference for the comment. Is there a way to change the code so that when the macro runs, it adds the comment to the cell that is currently selected?
I will need my hand held through this, as my VBA knowledge is very poor!
Thanks,
Simon
Sub addpicture()
' addpicture Macro
Range("I18").Select
Range("I18").AddComment
Range("I18").Comment.Visible = False
Range("I18").Comment.Text Text:="" & Chr(10) & ""
Range("I18").Select
ActiveCell.Comment.Visible = True
Range("I18").Comment.Shape.Select True
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.RGB = RGB(0, 0, 0)
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 255, 255)
Selection.ShapeRange.Fill.BackColor.RGB = RGB(255, 255, 225)
Selection.ShapeRange.Fill.UserPicture _
"C:\Users\Simon\Pictures\Building blocks2.gif"
Selection.ShapeRange.LockAspectRatio = msoFalse
Selection.ShapeRange.Height = 127.5
Selection.ShapeRange.Width = 283.5
Range("I18").Select
ActiveCell.Comment.Visible = False
End Sub