Show Two Pics based on value of two cells

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,395
Hello,

I got the following code from this forum itself that works great:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim oPic As Picture
Me.Pictures.Visible = False
If Target.Address <> "$A$1"Then Exit Sub
With Range("A1")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
With Range("A10")
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
End With
Exit For
End If
Next oPic
End With
End Sub
But when I change it to the following code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim oPic As Picture
Me.Pictures.Visible = False
If Target.Range <> ("$A$1""$J$1") Then Exit Sub
With Range("A1")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
With Range("A10")
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
End With
Exit For
End If
Next oPic
End With
With Range("J1")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
With Range("J10")
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
End With
Exit For
End If
Next oPic
End With
End Sub
It does not work with cell J1. I am not sure what do i have to do to make it work. I want both pictures to show up when two different values are selected in those two cells.

Thanks
Asad
 
Last edited:

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,197
Does this do what you want?


Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim oPic As Picture
Me.Pictures.Visible = False

With Target
Select Case .Address

Case "$A$1"
For Each oPic In Me.Pictures
If oPic.Name = .Text Then

With Range("A10")
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
End With
Exit For
End If
Next oPic

Case "$J$1"
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
With Range("J10")
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
End With
Exit For
End If
Next oPic

End Select
End With
End Sub
 

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,395
Thanks for the answer Tom.
It is beautiful. But why does the first picture disappear when I select the other one?
Is it possible for both pictures to stay if I got two different values in both cells? If either one does not have a vlaue, then of course only one picture will show up.
Is it possible?
Thanks a lot.
Asad
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,197
Thanks for the answer Tom.
It is beautiful. But why does the first picture disappear when I select the other one?
Because that was what your original code was doing. Notice in your first post, before anything else, immediately below both Sub lines you are hiding all pictures as soon as anything is changed.


Is it possible for both pictures to stay if I got two different values in both cells? If either one does not have a vlaue, then of course only one picture will show up. Is it possible?
Yes it is possible but a little confusing. Explain your potential scenarios so this can get nailed down the way you want. Does "two different" mean two unequal, or just each cell (A1 and J1) having some value.
 

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,395
I will try to explain a bit more.

I will have either one vlaue in one of the cells (A1, J1) or two different values in them. It will never be same value in both cells, unless they are empty.
So can I have the picture or pictures to show up corressponding to the values in cells?
  1. If there is a value in A1 but nothing in J1, only one picture will show in A10
  2. If A1 is blank, but J1 has a value, then that picture will show in J10
  3. If both A1 and J1 have values, both pictures should show up in A10 and J10 respectively
  4. If boh are empty, no picture to show
Thanks
Asad
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,197
Replace the last code I posted with this and see if it does the dirty deed.


Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim oPic As Picture
Me.Pictures.Visible = False

With Target
Select Case Target.Address

Case "$A$1"
For Each oPic In Me.Pictures
If oPic.Name = .Text Then

With Range("A10")
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
End With
Exit For
End If
Next oPic
If Len(Range("J1").Value) > 0 Then _
ActiveSheet.Pictures(Range("J1").Text).Visible = True
 
Case "$J$1"
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
With Range("J10")
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
End With
Exit For
End If
Next oPic
If Len(Range("A1").Value) > 0 Then _
ActiveSheet.Pictures(Range("A1").Text).Visible = True

End Select
End With
End Sub
 

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,395
Yes, That's the one.

Thanks a lot Tom.
It does all the dirty work for me:biggrin:

Thanks again
Asad
 

Forum statistics

Threads
1,081,849
Messages
5,361,673
Members
400,644
Latest member
ndroger1

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top