|
|
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:
[Quoted Text] > 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:
[Quoted Text] > 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(iItem, 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:
[Quoted Text] > 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(iItem, 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:
[Quoted Text] > 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:
[Quoted Text] > 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:
[Quoted Text] > 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:
[Quoted Text] > 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:
[Quoted Text] > 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:
[Quoted Text] > 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:
[Quoted Text] > 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_FileLoc_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:
[Quoted Text] > 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
|
|
|