How to extract value from cell with formula other than using the direct change in cell

[kelly mort]

Hello All,
How do I make this code respond to cells with formulasome instead of the Target?

I am running into some troubles and wish there is a way to use cells with formulas instead of direct changing from the cells.
Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
Dim fPath As String, sFile$
If Target = Me.[h9] Then
    fPath = ThisWorkbook.Path & "" & Me.CmbTerm.Text
    sFile = Dir(fPath & "" & Right(Me.[h9].Text, 3) & ".*")
    If sFile <> vbNullString Then
      Me.Image1.Picture = LoadPicture(fPath & "" & sFile)
    Else
        Me.Image1.Picture = LoadPicture("")
    End If
    If Err.Number = 53 Then Me.Image1.Picture = LoadPicture("")
End If
End Sub

 

[dmt32]

Hi,
To work with Cell Changes with Formula's you will nee to use Calculate event. Unfortunately, this event dose not have the range parameter Target so you will need to employ a little bit of trickery by using a cell or it's ID property as a place holder for your formula to compare changes f value against.

Untested but made some changes to your code - see if it does what you want

Private Sub Worksheet_Calculate()
Dim fPath As String, sFile$
On Error GoTo exitsub
With Me.[h9]
    If .Value <> .ID Then
    Application.EnableEvents = False
    fPath = ThisWorkbook.Path & "" & Me.CmbTerm.Text
    sFile = Dir(fPath & "" & Right(Me.[h9].Text, 3) & ".*")
    If sFile <> vbNullString Then
      Me.Image1.Picture = LoadPicture(fPath & "" & sFile)
    Else
        Me.Image1.Picture = LoadPicture("")
    End If
    .ID = .Value
End If
End With


exitsub:
If Err.Number = 53 Then Me.Image1.Picture = LoadPicture("")
Application.EnableEvents = True
End Sub

may need some adjustment but hopefully give some idea.

Dave

 

[kelly mort]

Hello Dave,
The code worked just like magic!!!

Thanks a lot again
Kelly