How do I lock an image into a cell and be able to sort?

[Guest]

Hello.
We use excel 2002 for jewelry proposals. The first column is the images of
the jewelry and the second column is the item number. We put hundreds of
images onto these spreadsheets but are unable to sort them because they can't
be locked into the cell. Is there a solution? Any help would be greatly
appreciated!!

 

[Jason Morin]

AFAIK you can't directly insert an image into a cell. I
would suggest creating a dropdown menu in another part of
the worksheet or new worksheet that will bring up the
images. JE McGimpsey's website has detailed instructions
here:

http://www.mcgimpsey.com/excel/lookuppics.html

HTH
Jason
Atlanta, GA

 

[Guest]

sorry. no help. no solution. image controls are objects
that "float" on top of the sheet. xl does not profide a
way to "lock" them to a cell and no code to sort them.
the only work around would be to create a form that has
the image control on it with a text box for the item
number. input the item number and it calls the image. you
would have to put the image's file path in your data on
the row with the item number. that would be sortable. the
code behide the form would change the file path to match
the item number.
i am working on something similar now but have not finished
here is what i have so far.
Private Sub CommandButton1_Click()
Dim picpath As String
Dim rng As Range

Set myrng = Range("A1")
picpath = "H:\" & myrng.Value
Application.EnableEvents = False

Image1.Picture = LoadPicture(picpath)

End Sub
this was a test to see that it could be done. i simply put
a file path in cell a1. it works. but to work like we both
want it to, myrng would have to be a vlookup or something
similar. or something that would lookup the item number
that i input into the form's text box that would make
myrng = items file path. i haven't gotten that far.
sorry i'm no help
good luck

 

[Guest]

hi again
I worked on it a little more. and solved it.
a form with 1 command buttom, 1 text box, 1 image control
test ItemID in range ak5:AK7
test pic name in range AL5:AL7
lookup formula =LOOKUP(AN3,AK5:AK7,AL5:AL7) at AO3
code behind the button:

Private Sub CommandButton1_Click()
Dim picpath As String
Dim myrng As Range
Dim zim As Range
Set zim = Range("AN3")
If IsNull(Me.txtItemID) = True Then
MsgBox "nothing to find!?!?!"
Me.txtItemID.SetFocus
Else
zim = Me.txtItemID
End If

Set myrng = Range("AO3")
picpath = "H:\" & myrng.Value
Application.EnableEvents = False

Image1.Picture = LoadPicture(picpath)

End Sub
it works!
Tested!
grin!