+ Reply to Thread
Results 1 to 1 of 1

Macro - insert pic in comment box multiple issues

  1. #1
    Registered User
    Join Date
    03-31-2014
    Location
    San Antonio, TX
    MS-Off Ver
    2007
    Posts
    1

    Macro - insert pic in comment box multiple issues

    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.
    Last edited by JLOCKNER; 04-08-2014 at 03:21 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Insert multiple Photos MACRO
    By yazl in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 02-19-2014, 10:26 AM
  2. Replies: 0
    Last Post: 11-08-2012, 01:07 PM
  3. multiple issues with Macro grouping
    By Kezwick in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-14-2012, 04:44 AM
  4. Insert multiple macro Rows with sum
    By karvan in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-24-2011, 11:13 PM
  5. Insert a particular macro in multiple workbooks at once
    By dollar in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-17-2009, 06:07 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1