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

Can you tell me what "InsertPicFromFile _" module you are using?
It is giving me a Sub or Function not defined error on that line.
I tried importing the "InsertPicFromFile _" mod from the contextures
example, changed the ref to the strFileLoc to match your ref. It runs but
still does not bring the pic up.

I set up a simple example on a new worksheet, placed and linked a combobox
on cell D6 and set its linked range to j6:j8, where I put the names of the
pics. I put the full paths next to each one in k6:k8. (To test the path I
added =hyperlink and was able to open the pic by clicking on it.) In J3 I
wrote a vlookup so it will display the path of the pic selected in the
combobox, and named this cell "LU_Name_FileLoc_XRef".
I selected cells D9:D13 to be the "rngPicDisplayCells".

In the VBA worksheet (sheet1 code) combobox1 I put your Sub under the change
event.

What am I missing?

"Bill Manville" wrote:

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

Squeaky wrote:
Can you tell me what "InsertPicFromFile _" module you are using?

I downloaded it from contextures yesterday: showfilepicsdemo.zip.

In J3 I
wrote a vlookup so it will display the path of the pic selected in the
combobox, and named this cell "LU_Name_FileLoc_XRef".

Ah - that's the mistake
In the contextures file that name references the 2-column table with
picture names and paths.
My code uses the listindex of the combobox to reference the path from the
appropriate row of column 2 of that table

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

So what cells should I name "LU_Name_FileLoc_XRef"? (in my example)

I deleted the module and started getting the error again. I renamed the
combobox as iItem and the error went away but I still couldn't make this
work. Can you send me your workup so I can study it?

squeakysneakers at yahoo

"Bill Manville" wrote:

Squeaky wrote:
Can you tell me what "InsertPicFromFile _" module you are using?

I downloaded it from contextures yesterday: showfilepicsdemo.zip.

In J3 I
wrote a vlookup so it will display the path of the pic selected in the
combobox, and named this cell "LU_Name_FileLoc_XRef".

Ah - that's the mistake
In the contextures file that name references the 2-column table with
picture names and paths.
My code uses the listindex of the combobox to reference the path from the
appropriate row of column 2 of that table

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

Squeaky wrote:
So what cells should I name "LU_Name_FileLoc_XRef"? (in my example)

The table with 2 columns containing picture name in column 1 and picture
path in column 2, just as it is in the contextures example.

I deleted the module and started getting the error again. I renamed the
combobox as iItem and the error went away but I still couldn't make this
work

I don't understand what module you deleted or why, nor why you would rename
a combobox with the name of the integer variable I used to hold its
listindex (+1).

Can you send me your workup so I can study it?

I didn't keep my previous attempt but I will do it again if you want.
Tell me where to send it.

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

I'm still kinda new at VBA and still getting used to the many definitions. I
will attempt to make it work by your instructions but I'm better when I can
see a working model.

My email is "squeakysneakers at yahoo dot com"

"Bill Manville" wrote:

Squeaky wrote:
So what cells should I name "LU_Name_FileLoc_XRef"? (in my example)

The table with 2 columns containing picture name in column 1 and picture
path in column 2, just as it is in the contextures example.

I deleted the module and started getting the error again. I renamed the
combobox as iItem and the error went away but I still couldn't make this
work

I don't understand what module you deleted or why, nor why you would rename
a combobox with the name of the integer variable I used to hold its
listindex (+1).

Can you send me your workup so I can study it?

I didn't keep my previous attempt but I will do it again if you want.
Tell me where to send it.

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

Hi Bill,

Got your email. That's the same file I am trying to modify. It uses a data
validation box whereas I am trying to make it work with an active x combobox.

"Squeaky" wrote:

I'm still kinda new at VBA and still getting used to the many definitions. I
will attempt to make it work by your instructions but I'm better when I can
see a working model.

My email is "squeakysneakers at yahoo dot com"

"Bill Manville" wrote:

Squeaky wrote:
So what cells should I name "LU_Name_FileLoc_XRef"? (in my example)

The table with 2 columns containing picture name in column 1 and picture
path in column 2, just as it is in the contextures example.

I deleted the module and started getting the error again. I renamed the
combobox as iItem and the error went away but I still couldn't make this
work

I don't understand what module you deleted or why, nor why you would rename
a combobox with the name of the integer variable I used to hold its
listindex (+1).

Can you send me your workup so I can study it?

I didn't keep my previous attempt but I will do it again if you want.
Tell me where to send it.

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

Squeaky wrote:
That's the same file I am trying to modify. It uses a data
validation box whereas I am trying to make it work with an active x combobox.


Sorry - sent the wrong version by accident.

I have re-made the modifications, limited to adding the combobox to the sheet,
setting its listfillrange and adding the ComboBox1_Change event procedure to
the sheet's module. Sending it now by email


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

Hi Bill,

Thanks for the file. Very Helpful. I found that the problem lies with
'LU_Name_FileLoc_XRef'. When I set that range to be on another sheet (like
sheet2) on the same workbook, it will not work. I get:
Run-Time error 1004:
Mothod 'Range' of object'_worksheet' failed.
How can I get it to reference sheet2?



"Bill Manville" wrote:

Squeaky wrote:
That's the same file I am trying to modify. It uses a data
validation box whereas I am trying to make it work with an active x combobox.


Sorry - sent the wrong version by accident.

I have re-made the modifications, limited to adding the combobox to the sheet,
setting its listfillrange and adding the ComboBox1_Change event procedure to
the sheet's module. Sending it now by email


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

ThisWorkbook.Sheets("Sheet2").Range("LU_Name_FileL oc_XRef")

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