Hi All,

I have been reading many posts about using code to insert pictures from a
folder into an area of a spreadsheet and have made them work according to the
many people who have posted here. Most posts say that either you enter a name
or number in a cell that corresponds to a pic, or use a data validation box
to do a lookup. Again, I have made each of these methods work. My question:
Can you make it work using an Active X combo box? I am able to make all the
code work, but the picture just does not materialize. Alternately are you
able to make a data validation box perform an auto complete as the combo box
does?
Basically I have 8000 enrties of part numbers and with the combo box I can
start typing the first numbers or letters and the box jumps to that part of
the list.

Thanks in advance.

Squeaky

Have you tried linking the ActiveX combo box to a cell (via its
LinkedCell property) and using the cell in the method that you are
using to load the picture?

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Hi Bill,

Yes I have. All of the formulas work, even a hyperlink formula that I click
on will open the pictures as I make my selection in the combobox. It's almost
like the macro does not recognize that the value in it has changed.

"Bill Manville" wrote:

Have you tried linking the ActiveX combo box to a cell (via its
LinkedCell property) and using the cell in the method that you are
using to load the picture?

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

How are you trying to trigger the macro?

It would seem the linked cell doesn't trigger the Worksheet_Change
event as I had assumed it would. So that leaves 2 possibilities:
ComboBox_Change event (where you will need to check that a match has
been generated - .ListIndex=0), or Worksheet_Calculate with a formula
somewhere referencing the linked cell.

If you can't make it work, please post the macro you are trying to run.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

I followed the example I got from the "contextures" page
http://www.contextures.on.ca/excelfiles.html#DataVal.
When I use the data validation box the macro works just fine and the
pictures will change.
Basically I replaced the data validation box with a combobox. I am trying to
trigger the macro by selecting an item from the combobox.
I'm not sure how to check the 2 items you noted. I did try putting the macro
code into the combox_change event but kept getting an error.

"Bill Manville" wrote:

How are you trying to trigger the macro?

It would seem the linked cell doesn't trigger the Worksheet_Change
event as I had assumed it would. So that leaves 2 possibilities:
ComboBox_Change event (where you will need to check that a match has
been generated - .ListIndex=0), or Worksheet_Calculate with a formula
somewhere referencing the linked cell.

If you can't make it work, please post the macro you are trying to run.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

This event procedure worked fine for me

Private Sub ComboBox1_Change()
Dim iItem As Integer
iItem = Me.ComboBox1.ListIndex + 1
If iItem = 1 Then
InsertPicFromFile _
strFileLoc:=Range("LU_Name_FileLoc_XRef").Cells(iI tem, 2), _
rDestCells:=Range("rngPicDisplayCells"), _
blnFitInDestHeight:=True, _
strPicName:="MyDVPic"
End If
End Sub

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Hi Bill,

I found a work around in the meantime but I will adjust it as per your
instructions. Thank you very much for your over-the-top patience and
assistance. I have created an inventory database and now due to your
assistance it will be complete with pictures. You have a fan.

"Squeaky" wrote:

Hi All,

I have been reading many posts about using code to insert pictures from a
folder into an area of a spreadsheet and have made them work according to the
many people who have posted here. Most posts say that either you enter a name
or number in a cell that corresponds to a pic, or use a data validation box
to do a lookup. Again, I have made each of these methods work. My question:
Can you make it work using an Active X combo box? I am able to make all the
code work, but the picture just does not materialize. Alternately are you
able to make a data validation box perform an auto complete as the combo box
does?
Basically I have 8000 enrties of part numbers and with the combo box I can
start typing the first numbers or letters and the box jumps to that part of
the list.

Thanks in advance.

Squeaky