multiple dynamic picture on excel

[sergenelles]

I want to create a flexible report that I can print out and give my customer.
I make a list of product we sell, and I like to have the picture of the selected product on the left.

I have the VBA that can make change the picture, but i can make it work for only 1 picture has the "worksheet change" vba can only be used for 1 time

I don't know how to make it repeat for all the needed cells.

such system is so good for all people who need many pictures to be updated.

thank you

serge

 

[careywalker]

I would have thought the Worksheet_Change event would run each time the worksheet changes. Can you explain a bit more the steps you want to happen and then maybe I can help with a solution.

 

[sergenelles]

Hello,

Yes you are right, every time there is a change, it changes the indicated cell.
But we can indicate only 1 target, but I have so many cells that I need to change.


here is the code for VBA
--------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
InsertPicFromFile _
strFileLoc:=Range("fileroot").Value, _
rDestCells:=Range("picok"), _
blnFitInDestHeight:=True, _
strPicName:="picselect"
End Sub


Sub InsertPicFromFile( _
strFileLoc As String, _
rDestCells As Range, _
blnFitInDestHeight As Boolean, _
strPicName As String)

Dim oNewPic As Shape
Dim shtWS As Worksheet

Set shtWS = rDestCells.Parent

On Error Resume Next
'Delete the named picture (if it already exists)
shtWS.Shapes(strPicName).Delete

On Error Resume Next
With rDestCells
'Create the new picture
'(arbitrarily sized as a square that is the height of the rDestCells)
Set oNewPic = shtWS.Shapes.AddPicture( _
Filename:=strFileLoc, _
LinkToFile:=msoFalse, _
SaveWithDocument:=msoTrue, _
Left:=.Left + 1, Top:=.Top + 1, Width:=.Height - 1, Height:=.Height - 1)

'Maintain original aspect ratio and set to full size
oNewPic.LockAspectRatio = msoTrue
oNewPic.ScaleHeight Factor:=1, RelativeToOriginalSize:=msoTrue


------------------------------------

as you can see the destination cell is at only 1 place, can not be at different cells in the same time.

thank you

serge

 

[sergenelles]

sorry, i forgot to say that I use excel 2003.

also it must have a way to indicate the target string for each pictures.

thank you

serge

 

[careywalker]

The Range object that gets passed in contains all of the cells that changed. So what you need to do is loop through each range in the Range object and then call the sub InsertPicFromFile. So your Worksheet_Change sub would look something like, if not exactly like,...

Private Sub Worksheet_Change(ByVal Target As Range)

Dim changedRange as Range
for each changedRange in Target

InsertPicFromFile _
strFileLoc:=Range("fileroot").Value, _
rDestCells:=Range(changedRange.Address), _
blnFitInDestHeight:=True, _
strPicName:="picselect"
​

next
​

End Sub

 

[sergenelles]

I tried, it doesn't work

there is yellow hi-light for these words

InsertPicFromFile _
strFileLoc:=Range("fileroot").Value, _
rDestCells:=Range("picok"), _
blnFitInDestHeight:=True, _
strPicName:="picselect"
End Sub

 

[careywalker]

What was the error message that was displayed?

 

[sergenelles]

I have first a message miss match,
but now i changed the things a bit,
and i have a picture to appear where is the active cell,

if i move the mouse, change the item reference,
the picture disappears, and appear in the new active cell.

if i move the mouse but don't try to change any reference number,
the picture stays at the first cell

also the picture in not changing of "picture". it stays at the same picture,
just move of place

I also wonder what made the computer select this picture and not another

funny,..

thank you to try,..

serge

 

[Datsmart]

Take a look at the way pictures are handled here:
http://www.mcgimpsey.com/excel/lookuppics.html

This may help you with what you are trying to do.

 

[sergenelles]

Thank you John,
I have excel 2003, so i suppose it is a bit different.
this way, you need to make a copy of all your picture and make your excel file really heavy.
I wanted to find a VBA that takes the picture from another file.
Thank you for the suggestion
serge