VBA not working - Messy.
 

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_LTarget_Max_L,"PicOver",IF
(BilSuc_Gas_LTarget_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_LTarget_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.

VBA not working - Messy.
 

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

--
__________________________________
HTH

Bob

"NPell" wrote in message
...
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_LTarget_Max_L,"PicOver",IF
(BilSuc_Gas_LTarget_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_LTarget_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.