Insert/Remove photos depending up on ref. cell value changing

Solved/Closed

jinoob

Posts: 13
Registration date: Monday February 18, 2013
Status: Member
Last seen: October 1, 2013

- Feb 18, 2013 at 07:28 AM
KALEEM - Oct 29, 2014 at 08:33 AM

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.

Insert/Remove photos depending up on ref. cell value changing
How to remove photo from outlook - Guide
How to insert a photo in word resume - Guide
Insert the current date and time in cell a1 ✓ - Forum - Excel
How to insert rows in excel automatically based on cell value - Forum - Excel
In ms excel, if you want to insert the same formula in a given cell of different worksheets then which of the following action y - Guide

2 replies

Answer 1 / 2

rizvisa1

Posts: 4479
Registration date: Thursday January 28, 2010
Status: Contributor
Last seen: May 5, 2022

770
Feb 18, 2013 at 07:32 AM

see this
https://ccm.net/forum/affich-331409-excel-picture-inserting-macro-help-urgent#1

jinoob

Posts: 13
Registration date: Monday February 18, 2013
Status: Member
Last seen: October 1, 2013

Feb 20, 2013 at 06:07 AM

Please help me out to find a solution for this problem.
Requirement:-
1. If I type an image file name in A1 Cell(for eg: 4455)of Excel sheet, then the image/ photo of the same file no.should be displayed in B1 Cell.

2. If I cancel or change the file name in A1 Cell (For eg:- Instead of 4455, if I type 4465), then the previous photo in the B1 Cell according to the old file name should be deleted and the new photo as per the new file name given in A1 Cell should be displayed.

rizvisa1

Posts: 4479
Registration date: Thursday January 28, 2010
Status: Contributor
Last seen: May 5, 2022

770
Feb 20, 2013 at 06:18 AM

did you see that thread

jinoob

Posts: 13
Registration date: Monday February 18, 2013
Status: Member
Last seen: October 1, 2013

Feb 20, 2013 at 06:24 AM

ya. but that code not suitable for me.

jinoob

Posts: 13
Registration date: Monday February 18, 2013
Status: Member
Last seen: October 1, 2013

Feb 20, 2013 at 06:35 AM

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

rizvisa1

Posts: 4479
Registration date: Thursday January 28, 2010
Status: Contributor
Last seen: May 5, 2022

770
Feb 20, 2013 at 06:35 AM

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

rizvisa1

Posts: 4479
Registration date: Thursday January 28, 2010
Status: Contributor
Last seen: May 5, 2022

770
Feb 20, 2013 at 07:25 AM

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

Answer 2 / 2

KALEEM
Oct 29, 2014 at 08:33 AM

that's great. this solve may problem
thanks rizvisa1

Subscribe To Our Newsletter!