Display Images based on cell values in excel (ShowPicD)

         First of all  you have to give file folder names to jpeg images(photos) which have saved in local drives  then after give numbers like 1,2,3,4,5.... so on to student photos as in oreder of NR alphabetical order sheet - ----> Open excel sheet---->go to developer tab ----> click on visual basic --->select module under insert tab on popup window  ----> paste below code in module ---->then save the code --->return to active sheet ------>select any cell type this one:  =ShowPicD()       You place the location of the file in the ( ) ---->to select location of file (hyper link) press Ctrl + K  example("E:\latest photos 2015-16marvelly\"&()&".JPG" (enter JPEG  file name number in the bracket)  ----> you can change aspect ratio as changing red colour numbers as mentioned below   (click here model excel file)


Function ShowPicD(PicFile As String) As Boolean
'Same as ShowPic except deletes previous picture when picfile changes
Dim AC As Range
Static P As Shape
On Error GoTo Done
Set AC = Application.Caller
If PicExists(P) Then
P.Delete
Else
'look for a picture already over cell
For Each P In ActiveSheet.Shapes
If P.Type = msoLinkedPicture Then
If P.Left >= AC.Left And P.Left < AC.Left + AC.Width Then
If P.Top >= AC.Top And P.Top < AC.Top + AC.Height Then
P.Delete
Exit For
End If
End If
End If
Next P
End If
Set P = ActiveSheet.Shapes.AddPicture(PicFile, True, True, AC.Left, AC.Top, 120, 110)
ShowPicD = True
Exit Function
Done:
ShowPicD = False
End Function

Function PicExists(P As Shape) As Boolean
'Return true if P references an existing shape
Dim ShapeName As String
On Error GoTo NoPic
If P Is Nothing Then GoTo NoPic
ShapeName = P.Name
PicExists = True
NoPic:
PicExists = False
End Function

No comments:

Post a Comment

inspiration videos to teachers