Hi
Recently goggle a macro where it can insert picture in the comment.
Have try it but need help to add a line where it can lock picture aspect ratio. I've also goggle for an answer but it does work using the line :
.Comment.Shape.LockAspectRatio = msoTrue
Anyone ?
<table border="0" cellpadding="0" cellspacing="0" height="607" width="490"><colgroup><col style="mso-width-source:userset;mso-width-alt:18066;width:371pt" width="494"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt;width:371pt" height="17" width="494">Option Explicit</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">Const ImgFileFormat = "Image Files (*.bmp;*.gif;*.tif;*.jpg;*.jpeg)," & _</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17"> "*bmp;*gif;*.tif;*.jpg;*.jpeg"</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">Sub AddPicturesToComments()</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">Dim HasCom</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">Dim Pict As String</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">Dim Ans As Integer</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">Set HasCom = ActiveCell.Comment</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">If Not HasCom Is Nothing Then ActiveCell.Comment.Delete</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">Set HasCom = Nothing</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">GetPict:</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">Pict = Application.GetOpenFilename(ImgFileFormat)</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">'Note you can load in, almost any file format</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">If Pict = "False" Then End</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">Ans = MsgBox("Open : " & Pict, vbYesNo + vbExclamation, "Use this Picture?")</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">If Ans = vbNo Then GoTo GetPict</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">With ActiveCell</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17"> .AddComment</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17"> .Comment.Visible = False</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17"> .Comment.Shape.Fill.Transparency = 0#</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17"> .Comment.Shape.Fill.UserPicture Pict</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">End With</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">End Sub</td> </tr> </tbody></table>
Thanks
Recently goggle a macro where it can insert picture in the comment.
Have try it but need help to add a line where it can lock picture aspect ratio. I've also goggle for an answer but it does work using the line :
.Comment.Shape.LockAspectRatio = msoTrue
Anyone ?
<table border="0" cellpadding="0" cellspacing="0" height="607" width="490"><colgroup><col style="mso-width-source:userset;mso-width-alt:18066;width:371pt" width="494"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt;width:371pt" height="17" width="494">Option Explicit</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">Const ImgFileFormat = "Image Files (*.bmp;*.gif;*.tif;*.jpg;*.jpeg)," & _</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17"> "*bmp;*gif;*.tif;*.jpg;*.jpeg"</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">Sub AddPicturesToComments()</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">Dim HasCom</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">Dim Pict As String</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">Dim Ans As Integer</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">Set HasCom = ActiveCell.Comment</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">If Not HasCom Is Nothing Then ActiveCell.Comment.Delete</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">Set HasCom = Nothing</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">GetPict:</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">Pict = Application.GetOpenFilename(ImgFileFormat)</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">'Note you can load in, almost any file format</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">If Pict = "False" Then End</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">Ans = MsgBox("Open : " & Pict, vbYesNo + vbExclamation, "Use this Picture?")</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">If Ans = vbNo Then GoTo GetPict</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">With ActiveCell</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17"> .AddComment</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17"> .Comment.Visible = False</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17"> .Comment.Shape.Fill.Transparency = 0#</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17"> .Comment.Shape.Fill.UserPicture Pict</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">End With</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" height="17">End Sub</td> </tr> </tbody></table>
Thanks
