Can't find pictures in a referenced formula

[S Boak]

Hi Folks:

Need some help with a reference in a workbook I found for looking up
pictures - the book works great, but I owuld like to use other pictures, and
I can't find where the pictures are stored.

The book has 2 sheets - 1 and 2.

Sheet1 A2 is data-val cell pulling in one of four names from PicTable (on
Sheet 2 - a simple 2 Column 4 Row lookup )

Sheet 1 F1 displays the piture and has a formula:
=VLOOKUP(A2, PicTable, 2, FALSE)

I understand the formula, but can't find the pictures . . .


I'm bad with VB and the only code in the book I can find is:

Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
With Range("F1")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With
End Sub

I wd like to use different pictures and learn how to drive this for what I
need, but I can't find the pictures . . . . Would appreciate any help.

Thanks / Regards

 

[Ken Johnson]

Hi Folks:

Need some help with a reference in a workbook I found for looking up
pictures - the book works great, but I owuld like to use other pictures, and
I can't find where the pictures are stored.

The book has 2 sheets - 1 and 2.

Sheet1 A2 is data-val cell pulling in one of four names from PicTable (on
Sheet 2 - a simple 2 Column 4 Row lookup )

Sheet 1 F1 displays the piture and has a formula:
=VLOOKUP(A2, PicTable, 2, FALSE)

I understand the formula, but can't find the pictures . . .

I'm bad with VB and the only code in the book I can find is:

Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
With Range("F1")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With
End Sub

I wd like to use different pictures and learn how to drive this for what I
need, but I can't find the pictures . . . . Would appreciate any help.

Thanks / Regards

The pictures are on sheet 1 but all bar the one whose name is in F1
are not visible because their Visible property has been set to False.

You could add your own pictures by simply pasting them into sheet 1
and including their detail in the lookup table. Also you can delete
unwanted pictures by deleting them after making them visible and
deleting their details from the lookup table.

You can render all the pictures visible by temporarily commenting out
the second line (Me.Pictures.Visible = False) then separately
selecting each picture from the A1 drop down. That way all the
pictures will end up visible and on F1.

Ken Johnson

 

[S Boak]

Ken:
Many thanks . . . part way there.
Made the pictures visible, added a pic of my own, and expanded the lookup
table, but the new pic won't come up. I'm missing something I'm sure. How
to I label or tag the pic I added??

Thanks
Steve

 

[S Boak]

Ken:
A missing piece of info . . .
The data-val cell has the formula =OFFSET(PicTable,,,,1)
I understand OFFSET a little, but this is beyond me.
Steve

 

[Ken Johnson]

Ken:
A missing piece of info . . .
The data-val cell has the formula =OFFSET(PicTable,,,,1)
I understand OFFSET a little, but this is beyond me.
Steve

Hi Steve,

When you paste in one of your pictures Excel gives it a generic name
eg Picture 8. When that picture is selected its given name appears in
the Name Box that is on the left side of the Formula Bar (make sure
the Formula Bar is visible).

Say Picture 8 is a picture of "Orson CART" and you want its visibility
to be controlled by the code. All you need to do is add "Orson CART"
to column 1 of the PicTable and "Picture 8" to column 2 of the same
row in PicTable (both without the speech marks).

Then, when you type "Orson CART" into A2 on Sheet1 the formula in F1
is calculated to be "Picture 8" then the Code is run. The code first
makes all pictures on Sheet1 invisible then it loops through all the
pictures and makes visible only that one with its given name in F1.

One thing to check is that the Named Range PicTable actually contains
the information you typed in (Orson CART and Picture 8). If PicTable
is not a Dynamic Named Range then its address might need to be
extended to included added rows of information. This is done in the
Refers to: box on the Define Name dialog, which you get to by going
Insert|Name...|Define.

Ken Johnson

 

[Ken Johnson]

Hi Steve,

When you paste in one of your pictures Excel gives it a generic name
eg Picture 8. When that picture is selected its given name appears in
the Name Box that is on the left side of the Formula Bar (make sure
the Formula Bar is visible).

Say Picture 8 is a picture of "Orson CART" and you want its visibility
to be controlled by the code. All you need to do is add "Orson CART"
to column 1 of the PicTable and "Picture 8" to column 2 of the same
row in PicTable (both without the speech marks).

Then, when you type "Orson CART" into A2 on Sheet1 the formula in F1
is calculated to be "Picture 8" then the Code is run. The code first
makes all pictures on Sheet1 invisible then it loops through all the
pictures and makes visible only that one with its given name in F1.

One thing to check is that the Named Range PicTable actually contains
the information you typed in (Orson CART and Picture 8). If PicTable
is not a Dynamic Named Range then its address might need to be
extended to included added rows of information. This is done in the
Refers to: box on the Define Name dialog, which you get to by going
Insert|Name...|Define.

Ken Johnson

Hi Steve,

You shouldn't have to worry about that OFFSET formula, it just
extracts column 1 from the PicTable to use as the list shown in the
data validation drop down. When you add new rows of information to
PicTable, provided you increase its address to include the new rows,
the OFFSET formula includes those new rows in the Data Validation drop
down.

Ken Johnson

 

[Ken Johnson]

Hi Steve,

You shouldn't have to worry about that OFFSET formula, it just
extracts column 1 from the PicTable to use as the list shown in the
data validation drop down. When you add new rows of information to
PicTable, provided you increase its address to include the new rows,
the OFFSET formula includes those new rows in the Data Validation drop
down.

Ken Johnson

Hi Steve,

This is only a recommendation...

Convert PicTable to a Dynamic Named Range so that you don't have to
worry about changing its "Refers to" address every time a new row is
added or subtracted. Just go Insert|Name...|Define, click on PicTable
in the list of Names on the Define Name dialog, replace the formula in
the Refers to: box with...

=OFFSET(Sheet2!$A$1,1,0,COUNTA(Sheet2!$A:$A)-1,2)

Ken Johnson