Display Images based on cell values in excel (ShowPicD)

 FOLLOW BELOW STEPS TO INSERT IMAGE IN IDENTIFICATION REGISTER OF NOMINAL ROLE SSC  SOFTWARE 
A) IN COMPUTER :
1.CREATE FOLDER >>>>OPEN FOLDER>>>PASTE ALL PHOTOS OF SSC STUDENT IN         FOLDER>>>>GIVE ONLY NUMBERS LIKE 1,2,3,4, TO EACH PHOTO AS IN ORDER OF NR ALPHABETICAL ORDER SHEET
B) IN EXCEL 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