Show/Hide with VB... Need tailoring.

[TheMilkGuy]

Hi folks,

I know next to nothing about VB code, but even *I* know this is
ugly:

Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
Me.Pictures("Picture 1").Visible = True
With Range("F1")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = F1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("F2")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = F1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("F3")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = F2
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("F4")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = F2
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("F5")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = F1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("F6")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = F1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("F7")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = F1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("F8")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = F1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("F9")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = F1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("F10")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = F1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("F11")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = F1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("F12")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = F1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("F13")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = F1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("F14")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = F1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("F15")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = F1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
With Range("F16")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = F1
oPic.Left = .Left
Exit For
End If
Next oPic
End With
End Sub


Based on value (True/False) at F1. If true, an image appears.

These T/F values range from F1:F15... Could someone please simplify
this formula for me?

Thanks!

 

[Dave Peterson]

First, you write that you're looking at F1:F15, but your code finishes with F16.
I used F1:F16.

Second, you have a line: oPic.Top = F1
I'm guessing that you meant the top of each cell.

I didn't test this, but it compiled ok:

Option Explicit
Private Sub Worksheet_Calculate()
Dim oPic As Picture
Dim myCell As Range
Dim myRng As Range

Set myRng = Me.Range("F1:F16")

Me.Pictures.Visible = False
Me.Pictures("Picture 1").Visible = True

For Each myCell In myRng.Cells
With myCell
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
Next myCell

End Sub