I'm trying to create an electronic portfolio/assessment of my students' work in excel.
The spreadsheet lists several standards. For each standard there are four cells which contain grades(four grades per grading period per standard). I would like to attach pictures of the students' work in comment boxes on each cell that contains a grade. It would serve as an electronic portfolio of the students' work to share with parents as well as an electronic grade book. I am using the following code which works.
Option Explicit
Sub InsertComment()
'www.contextures.com\xlcomments03.html
Dim rngList As Range
Dim c As Range
Dim cmt As Comment
Dim strPic As String
On Error Resume Next
Set rngList = Range("C3:J3,C4:J4,C7:J7,C7:J7,C11:J11,C14:J14,C18:J18,C19:J19,C21:J21,C22:J22,C24:J24,C25:J25,C27:J27,C29:J29,C32:J32,C35:J35,C37:J37,C38:J38,C39:J39,C44:J44,C45:J45,C51:J51,C60:J60,C61:J61,C64:J64,C70:J70,C71:J71,C78:J78,C80:J80,C97:J99,C102:J102,C103:J103")
strPic = "C:\Users\133286\Desktop\Work Samples\"
For Each c In rngList
With c.Offset(0, 1)
Set cmt = c.Comment
If cmt Is Nothing Then
Set cmt = .AddComment
End If
With cmt
.Text Text:=""
.Shape.Fill.UserPicture strPic & c.Value
.Visible = False
.Shape.Height = 150
.Shape.Width = 200
End With
End With
Next c
End Sub
The first problem is that I cannot get this code to work beyond cell J103. My spreadsheet actually ends with J130, but beginning with C109:J109 the code stops working. Any idea what I am doing wrong?
My second problem is that the code only works on one page of the spreadsheet at a time and does not update the comment box pics as I update pictures of my students' work on my computer. Each time I upload pics from my ipad to my work samples folder on my computer I have to "delete all comments" and run the code on each page of the spreadsheet to get the comment boxes to come up. Is there a way to have the code work on every page of the spreasheet at once and update the pics in the spreadsheet as the pics are updated in my work samples folder? Thanks.
Bookmarks