Run Macro When Formula Result Changes

[redbaron06]

Hi everyone, I would really appreciate some VBA help.

I would like the macro below to run automatically if the cell "BO1" Changes from its previous value. Cell "BO1" is determinded by drop down menus on another worksheet. Any help would be greatly appreciated.

-Terry

___________________________

Sub DoGetData()
Dim strFile As String
Dim strRange As String
strFile = Range("BO1").Value
GetDataFromClosedWorkbook strFile, "A8:N200", ActiveCell, False
End Sub
'GetDataFromClosedWorkbook "C:\FolderName\WorkbookName.xls", _
' "A1:B21", Active - Cell, False
' GetDataFromClosedWorkbook "U:\MPLS\KAZ\306_KAZAKHSTAN__PSC_20100602.xls", "A1:J200", "A10", False
'

Sub GetDataFromClosedWorkbook(SourceFile As String, SourceRange As String, _
TargetRange As Range, IncludeFieldNames As Boolean)
Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset
Dim dbConnectionString As String
Dim TargetCell As Range, i As Integer
dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};" & _
"ReadOnly=1;DBQ=" & SourceFile
Set dbConnection = New ADODB.Connection
On Error GoTo InvalidInput
dbConnection.Open dbConnectionString ' open the database connection
Set rs = dbConnection.Execute("[" & SourceRange & "]")
Set TargetCell = TargetRange.Cells(1, 1)
If IncludeFieldNames Then
For i = 0 To rs.Fields.Count - 1
TargetCell.Offset(0, i).Formula = rs.Fields(i).Name
Next i
Set TargetCell = TargetCell.Offset(1, 0)
End If
TargetCell.CopyFromRecordset rs
rs.Close
dbConnection.Close ' close the database connection
Set TargetCell = Nothing
Set rs = Nothing
Set dbConnection = Nothing
On Error GoTo 0
Exit Sub
InvalidInput:
MsgBox "The source file or source range is invalid!", _
vbExclamation, "Get data from closed workbook"
End Sub

 

[redbaron06]

Never mind you a genius VoG! - thanks for all the help, I was clearing an important cell.

Best Regards,

Terry

 

[mehul7187]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Calculate()
Static OldVal As Variant
If Range("BO1").Value <> OldVal Then
    OldVal = Range("BO1").Value
    Call DoGetData
End If
End Sub

How can be this done for range of cells? I mean instead of BO1 if it is range BO1:BO150?
Actually I want macro which can speak cell value so I used application.speak.speech which works ok but real problem is whenever value changes of one cell it speaks all the range values BO1:BO150 all over again. this is the problem I'm facing when I'm applying above mentioned logic to range of cells? I will be really thankful if you can provide any fix for this.