VBA not working - Messy.

[NPell]

Hello, this is my code at the moment...


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

This works fine, with the formula in M5 being;
=IF(BilSuc_Gas_L>Target_Max_L,"PicOver",IF
(BilSuc_Gas_L>Target_Min_L,"PicWithin","PicUnder"))


Im trying to convert this to be self contained in VBA, but failing....

Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
Dim GraphPic As Integer

If BilSuc_Mth_L > Target_Max_L Then
GraphPic = "PicOver"
Else IF BilSuc_Gas_L>Target_Min_L Then GraphPic = "PicWithin"
GraphPic = "PicUnder"
End If


With Range("GraphPic")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = Range("M5").Top - 9.75
oPic.Left = Range("M5").Left - 3#
Exit For
End If
Next oPic
End With
End Sub



But this does not work, any ideas?
Thanks in advance.

 

[Bob Phillips]

Maybe this

Dim GraphPic As String

If Me.Range("BilSuc_Mth_L").Value > Me.Range("Target_Max_L").Value
Then
GraphPic = "PicOver"
Else IF Me.Range("BilSuc_Gas_L").Value >
Me.Range("Target_Min_L").Value Then
GraphPic = "PicWithin"
Else
GraphPic = "PicUnder"
End If

With Range("M5")
Set oPic = Me.Pictures(GraphPic)
oPic.Visible = True
oPic.Top = Range("M5").Top - 9.75
oPic.Left = Range("M5").Left - 3#
End With