Inserting Pictures onto a spreadsheet.
 

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

Inserting Pictures onto a spreadsheet.
 

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

Inserting Pictures onto a spreadsheet.
 

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

Inserting Pictures onto a spreadsheet.
 

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

Inserting Pictures onto a spreadsheet.
 

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

Inserting Pictures onto a spreadsheet.
 

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

Inserting Pictures onto a spreadsheet.
 

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

Inserting Pictures onto a spreadsheet.
 

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

Inserting Pictures onto a spreadsheet.
 

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

Inserting Pictures onto a spreadsheet.
 

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

Inserting Pictures onto a spreadsheet.
 

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

Inserting Pictures onto a spreadsheet.
 

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

Inserting Pictures onto a spreadsheet.
 

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

Inserting Pictures onto a spreadsheet.
 

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

Inserting Pictures onto a spreadsheet.
 

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

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

Inserting Pictures onto a spreadsheet.
 

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