A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Links and Linking
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Inserting Pictures onto a spreadsheet.



 
 
Thread Tools Display Modes
  #1  
Old October 27th, 2008, 08:36 PM posted to microsoft.public.excel.links
Squeaky
external usenet poster
 
Posts: 131
Default 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
  #2  
Old October 29th, 2008, 12:52 AM posted to microsoft.public.excel.links
Bill Manville
external usenet poster
 
Posts: 258
Default 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

  #3  
Old October 29th, 2008, 03:41 PM posted to microsoft.public.excel.links
Squeaky
external usenet poster
 
Posts: 131
Default 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


  #4  
Old October 29th, 2008, 07:08 PM posted to microsoft.public.excel.links
Bill Manville
external usenet poster
 
Posts: 258
Default 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

  #5  
Old October 29th, 2008, 08:18 PM posted to microsoft.public.excel.links
Squeaky
external usenet poster
 
Posts: 131
Default 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


  #6  
Old October 30th, 2008, 12:24 AM posted to microsoft.public.excel.links
Bill Manville
external usenet poster
 
Posts: 258
Default 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

  #7  
Old October 30th, 2008, 01:32 PM posted to microsoft.public.excel.links
Squeaky
external usenet poster
 
Posts: 131
Default 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 D913 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


  #8  
Old October 30th, 2008, 02:37 PM posted to microsoft.public.excel.links
Bill Manville
external usenet poster
 
Posts: 258
Default 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

  #9  
Old October 30th, 2008, 04:49 PM posted to microsoft.public.excel.links
Squeaky
external usenet poster
 
Posts: 131
Default 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


  #10  
Old October 30th, 2008, 07:36 PM posted to microsoft.public.excel.links
Bill Manville
external usenet poster
 
Posts: 258
Default 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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:01 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.