Running an excel macro off data change

[iceberg27]

Does anyone know how to start a vba macro automatically from within a module? I want run the macro whenever a value in lets say worksheet1.B5 changes. I would rather not have to use a control to initiate the start of the macro.

 

[RobBroekhuis]

You need to put a change event handler on your code page for worksheet1: double-click on the "excel object" named "worksheet1" in the project explorer window of VBE to create the code page, then select "worksheet" and "change" from the two dropdown menus above the code page. This inserts the event handler heading. In the body, put something like

if target.address="$B$5" then
..run your macro here
end if

This will trigger automatically whenever B5 is changed.
Rob

 

[iceberg27]

Thanks Rob, that is how I have done it in the past on the worksheet level, but I searched around and can not find how to do the same thing in a module (ie not in a worksheet). I just can't find a way to initiate a macro in a module without droping down some sort of control...

 

[RobBroekhuis]

Why not call the macro from the change event handler? Maybe I'm missing something?
Rob

 

[Nikita6003]

Rob's right: try using the following code in the On_Change event of the worksheet:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Address(False, False) = "B5" Then
        'Calling a Sub procedure is simple. You can leave out the CALL 
        'But you'll also need to leave out the bracket open and close 
        'if you're passing any parameters
        Call CalculateExcangeRate("EUR", "USD")
        'If you want to call a function assign its return value 
        'to a variable
        l_dExchangeRate = CalculateCost("EUR", "USD", 10.95)
    End If
End Sub

NB: will only work for a Public Sub or Function (if you don't specifically declare your routine'll be assumed a Public routine)

HTH

Cheers
Nikki ;-)