Insert Picture Based On Lookup Record

[Jhon Mustofa]

hello expert...

i have following problem...getting picture combine with lookup formula, like this :
i have 100 data record and 500 pictures...
1. my foto in folder d:/foto;
2. my target in sheet "form Bgn"
3. my data in sheet "Bangunan"
4. my lookup in cell AM8 in sheet "Form Bgn"

if i change in my lookup record number , the picture should be change - suitable...
i want place 4 pictures different for each record lookup...

how to place in 4 pictures but keep suitable with lookup record....

any help much appreciated...

john m

[Jhon Mustofa]

i found this code but i don't know how to use & work :
and what this help?

Dim cf1, cf2, cf3, cf4 As String

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Or (Target.Column <> 37 And Target.Row <> 10) Then
Exit Sub
Else
insertfoto
End If
End Sub
Sub sisipfoto()
cf1 = "d:\f_bg2a\" & Range("ak10:AK10") & "a.jpg"
cf2 = "d:\f_bg2a\" & Range("ak10:AK10") & "b.jpg"
cf3 = "d:\f_bg2a\" & Range("ak10:AK10") & "c.jpg"
cf4 = "d:\f_bg2a\" & Range("ak10:AK10") & "d.jpg"
Image1.Picture = LoadPicture(cf1)
Image2.Picture = LoadPicture(cf2)
Image3.Picture = LoadPicture(cf3)
Image4.Picture = LoadPicture(cf4)
End Sub

[kev_]

In your workbook, AM8 = 9
VLookUp matches record on row 41 (sheet "Bangunan")
For this match what are the file names for the 4 pictures?
Picture 1
Picture 2
Picture 3
Picture 4

thanks

[Jhon Mustofa]

thanks kev-

that's can be made ... i mean eg. for row 1 name picture start from :
picture1 - 1.jpg
picture2 - 2.jpg
picture3 - 3.jpg
picture4 - 4.jpg

for row2 :
picture1 - 5.jpg
picture2 - 6.jpg
picture3 - 7.jpg
picture4 - 8.jpg

etc....

[kev_]

I do not think that is a good way to name your files
It would be easier for you if you named your files based on the value in column A
So I suggest the following

EXAMPLES:
Row2 A2 = 14
4 pictures named:
14_1
14_2
14_3
14_4

Row41 A2 = 9
4 pictures named:
9_1
9_2
9_3
9_4

This avoids creating & doing a lookup, and the path to the pictures is simple:
"d:\foto\" & AM8 & "_1.jpg"
"d:\foto\" & AM8 & "_2.jpg"
"d:\foto\" & AM8 & "_3.jpg"
"d:\foto\" & AM8 & "_4.jpg"

Are you happy with that?

[Jhon Mustofa]

hi kev_...

it's macro code ??? or else:

This avoids creating doing a lookup, and the path to the pictures is simple:
"d:/foto/" & AM8 & "_1.jpg"
"d:/foto/" & AM8 & "_2.jpg"
"d:/foto/" & AM8 & "_3.jpg"
"d:/foto/" & AM8 & "_4.jpg"

i mean how to use it..would you use my attachment file...

[kev_]

Have all your images been saved the same size?
Do you want them to be the same size when inserted?

[Jhon Mustofa]

yes Kev_ you're right..
i want inserted all image in the same size...

[kev_]

Ok - we will do it with VBA - will update thread when I am back at my PC

[Jhon Mustofa]

thank you for your time & kindness....

[kev_]

Use the attached file for the first test

Assumptions
- images are named 1_01.jpg to 1_04.jpg ( 1 is the value in cell in column A)
- images are in folder "d:\foto"

Rectangle shapes have been renamed (Pic_1, Pic_2, Pic_3, Pic_4)
There is a button on the workbook to run the macro
- later you may prefer this to be automatic
- but button better for testing

To test
- save sample images in folder (correctly named)
- amend value in AM8
- click update

Why did I rename the rectangles?
- to make the VBA simpler

My testing:

FilesInFolder.jpg

9images.jpg

10images.jpg

VBA in sheet module

Please Login or Register  to view this content.

[Jhon Mustofa]

hi Kev_ thank you so much...but i can't see that picture..
how to rename rectangle shape? button "update" not work...
foto1.pngfoto2.png

[kev_]

You have used different image names

Mine = 1_1.jpg 1_2.jpg etc
Yours= 1_01.jpg 1_02.jpg etc

EITHER remove the zeros

OR amend the code and insert 0 like this:

Please Login or Register  to view this content.

EDIT
I understand what happened - my explanation was bad in post#11 - sorry

[Jhon Mustofa]

hi Kev_you're brilliant!! Amazing , worked great...
if you have time and would you hlp me...i want to print that file/report but not one by one record..
how to make code that i want can print record 1 to 10 or print record 2 to 100..

any help..greatly appreciated....

[kev_]

Please mark thread as solved (click Thread Tools at top of thread)

I am busy next 3 days
What are you wanting to print for each record - is it the sheet with the pictures?

[Jhon Mustofa]

Please mark thread as solved (click Thread Tools at top of thread)

I am busy next 3 days
What are you wanting to print for each record - is it the sheet with the pictures?

yes..Kev_i want to print the sheet included pictures.....
i'm patient waiting for you....

thank for your kindness.....

[kev_]

run from sheet containing pictures

Please Login or Register  to view this content.

[Jhon Mustofa]

hi Kev_

thank but not fully working..

after using your macro...the result is showing print preview before for each page.
i want after using your macro, then directly printing not showing print preview before.

[kev_]

Please Login or Register  to view this content.

[daboho]

Tinggal ketik saja di range
Star dan end
star = Am8,end =Am9
And add to kev coding

Please Login or Register  to view this content.

[Jhon Mustofa]

hi...

how to print multiple records but specific pages. i want to print e.g record 1 to 20 but only page 1 not included page 2
thanks guys...

[kev_]

What are you asking?
Do you want to list specific records and only print them?

Eg print 1, 5, 9, 16, 20
Or
Print 1 - 20 but do not print 2, 5, 9
Or
Something else?

[Jhon Mustofa]

hi Kev_

thank for your time...

my file contains 2 pages..

eq. print 1-5 or 4-100 but only page 1 that printed not printed all pages

[daboho]

Please Login or Register  to view this content.

[Jhon Mustofa]

hi daboho...
how to use it :

Sheets("Sheet1").PrintOut From:=1, To:=1

add to your code before?

[Jhon Mustofa]

What are you asking?
Do you want to list specific records and only print them?

Eg print 1, 5, 9, 16, 20
Or
Print 1 - 20 but do not print 2, 5, 9
Or
Something else?

hi Kev_

you're right..i need specific records to print them...
eg. print 1-5,8,20

would you help me to figure it this?

[Rob1970]

Hi kev,

I've been looking at the post and it brilliant! I am new to VBA. I would like to kindly ask how to run it automatically? I have a drop down list.