Menu
Ask a question Report

Insert/Remove photos depending up on ref. cell value changing [Solved/Closed]

Posts
13
Registration date
Monday February 18, 2013
Last seen
October 1, 2013
 - - Latest reply:  KALEEM - Oct 29, 2014 at 08:33 AM
Excel Ask a question Report
Hello,
I designed a Student Profile in excel using LOOKUP function. In this profile, near the Student Name title I created one drop down list of students. If I select a student, then Roll No, Admission No, Date of Admission, Date of Birth, Mother's Name, Father's Name, Residential Address etc are shown downwards one by one in the respective cell automatically. I also need to insert his/her passport size photo in the prescribed cell, according to the Admission Number cell value. All the students' photos are put in one folder, each one named as Admission Number. First I select one student name from the drop down list displayed in his/her profile page, then printout will be taken. After taking that page printout, I will select another student. That time the photo of the previous student should be removed and the photo of the newly selected student should be displayed. If you know how it works (using any function/macro) please help me.
See more 

2 replies

Report Respond to rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
 914
0
Thank you
see this
http://ccm.net/forum/affich-331409-excel-picture-inserting-macro-help-urgent#1
View all 7 comments
Report
jinoob
Posts
13
Registration date
Monday February 18, 2013
Last seen
October 1, 2013
 -
ya. but that code not suitable for me.
Report
jinoob
Posts
13
Registration date
Monday February 18, 2013
Last seen
October 1, 2013
 -
Now I using below mentioned code. But this is not satisfy my requirements.
Sub Picture()
Range("B1").Select 'This is where picture will be inserted
Dim picname As String
picname = Range("A1") 'This is the picture name
ActiveSheet.Pictures.Insert("C:\Users\JINOOB\Desktop\I A\" & picname & ".jpg").Select 'Path to where pictures are stored
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This resizes the picture
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
With Selection
.Left = Range("B1").Left
.Top = Range("B1").Top
.ShapeRange.LockAspectRatio = msoFalse
.ShapeRange.Height = 95#
.ShapeRange.Width = 80#
.ShapeRange.Rotation = 0#
End With
End Sub
Report
rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
 914 -
Why you think it is not suitable?

you need to have a 
Private Sub Worksheet_Change(ByVal Target As Range)

End Sub


inside you can call that routine

I am not saying its 100%, but it is 95%. All it lack is delete
Report
rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
 914 -
Sorry did not see your code

this is what you need to do 

Sub Picture()
Dim picname As String
    
    Range("B1").Select 'This is where picture will be inserted

    picname = Range("A1") 'This is the picture name
    
    On Error Resume Next
    'delete previous pic
    ActiveSheet.Pictures("ProfilePicture").Delete
    Err.Clear
    Err.Number = 0
    On Error GoTo 0
    
    If (Dir("C:\Users\JINOOB\Desktop\I A\" & picname & ".jpg") = vbNullString) Then Exit Sub
    
   ActiveSheet.Pictures.Insert("C:\Users\JINOOB\Desktop\I A\" & picname & ".jpg").Select 'Path to where pictures are stored
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' This resizes the picture
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''
    With Selection
        .Name = "ProfilePicture"
        .Left = Range("B1").Left
        .Top = Range("B1").Top
        .ShapeRange.LockAspectRatio = msoFalse
        .ShapeRange.Height = 95#
        .ShapeRange.Width = 80#
        .ShapeRange.Rotation = 0#
    End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If (Intersect(Target, Range("A1")) Is Nothing) Then
        Exit Sub
    End If
    
    Application.EnableEvents = False
        Call Picture
    Application.EnableEvents = True
End Sub
Report
jinoob
Posts
13
Registration date
Monday February 18, 2013
Last seen
October 1, 2013
 -
Dear Sir,
This mail is just to thank you for your great help. I did it as per the suggestions and instructions given by you and now I am highly satisfied with the result. Now my work is almost 99% success. If I have any doubt, I will keep in touch with You. Let me thank you once again from the bottom of my heart for your timely help.Thanks a lot......

Thanks & Regards

Yours lovingly
Jinoob K Viswanathan.
Report Respond to KALEEM
0
Thank you
that's great. this solve may problem
thanks rizvisa1
Ask a question