Graphics to change in dropdown

[Potsy]

Hi

I have loked on www.mcgimpsey.com and there are instructions on how to
change a graphic when the user name changes. I have the data list
validated with the names and Picture1 etc on sheet title "Names" cell
range "=Names!$A$2:$B$5" and on another sheet titled "Cover Sheet" I
have the drop down linking to the "Names" sheet by Insert>Name>Define
and calling it "PicTable". Then I have inserted the following VLOOKUP
on "Cover Sheet": =VLOOKUP(A34, PicTable, 2, FALSE) which is pulling
in the names correctly but only pulling in the text Picture1, Picture2
etc and not the actual picture.

I have entered the following code in Visual Basic editor under the
"Cover Sheet" tab


Option Explicit

Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
With Range("A32")
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


The problem is the pictures held on 'Cover Sheet' do not show,
although text is correct on VLOOKUP with picture number, the coding is
also now hiding other graphics - e.g. our logo on the cover sheet.

Can anyone help as not experienced in coding, but okay in excel.

Is there anyway to do this outside visual basic?

 

[Dave Peterson]

I think you'll need VBA.

JE's code hides all the pictures with this line:
Me.Pictures.Visible = False

And only unhides the picture that has a name that matches that name in A32.

Are you sure you named the pictures the same as values returned in A32? I'm
guessing that there was at least a few typing mismatches.

After you double check (and make some changes <vbg>), you can try this version
of JE's code:

Option Explicit
Private Sub Worksheet_Calculate()
Dim oPic As Picture
With Range("A32")
For Each oPic In Me.Pictures
Select Case LCase(oPic.Name)
Case LCase("LogoPictName"), LCase("AnotherPictAlwaysVisible")
'do nothing
Case Is = LCase(.Text)
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Case Else
oPic.Visible = False
End Select
Next oPic
End With
End Sub

 

[Potsy]

I think you'll need VBA.

JE's code hides all the pictures with this line:
Me.Pictures.Visible = False

And only unhides the picture that has a name that matches that name in A32.

Are you sure you named the pictures the same as values returned in A32? I'm
guessing that there was at least a few typing mismatches.

After you double check (and make some changes <vbg>), you can try this version
of JE's code:

Option Explicit
Private Sub Worksheet_Calculate()
Dim oPic As Picture
With Range("A32")
For Each oPic In Me.Pictures
Select Case LCase(oPic.Name)
Case LCase("LogoPictName"), LCase("AnotherPictAlwaysVisible")
'do nothing
Case Is = LCase(.Text)
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Case Else
oPic.Visible = False
End Select
Next oPic
End With
End Sub













--

Dave Peterson- Hide quoted text -

- Show quoted text -

thanks for your help Dave but how do i name the picture? I have looked
in name>define on example but cannot see reference to pics or where
the original picturees are stored for that matter....

 

[Dave Peterson]

There is no link back to the original source when you insert a picture.

But you can select the picture, then type the new name in the namebox (to the
left of the formulabar).

Remember to hit enter after you type the new name.

 

[Potsy]

thanks for your help Dave but how do i name thepicture? I have looked
in name>define on example but cannot see reference to pics or where
the original picturees are stored for that matter....- Hide quoted text -

- Show quoted text -

Dave - just cracked it - thanks for your help