run macro after cell changes

[rjplante]

I have a formula in cell BF5 that monitors two other cells and when they have data entered the cell is populated. I would like to have a save macro called. I have the code listed below and whenever I click back to the worksheet to enter the data, I get a message box that says "compile error: Sub or Function not defined." and then the line in red is highlighted yellow and the line in blue is selected.

I don't know how to get the macro to work.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Range("BF5").Value <> "" Then
    Call Execute_save
End If

End Sub

 

[Sheltonite]

When you call a module you've created, you need to also call the sub. I've added a bit to your code. If your Module is Named "Execute_save" then you still need to call the sub. Make Sense?


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Range("BF5").Value <> "" Then Call Module1.Execute_save

End If

End Sub

 

[rjplante]

My save macro is titled Execute_save. I am not sure what a module is.

 

[Sheltonite]

When you record a Macro, it creates a Module that can be viewed/edited in Visual Basic. With the spreadsheet active, Press Alt + F11 to open the VBA window. In the Left-Hand Pane you should see a The VBA Project and folders titled "Miscrosoft Excel Objects" and "Modules". Expand the Modules folder to see what modules are there. If there is more than one module, double-click them until you find the module that contains your "Execute_Save" code. This module is likely called "Module1" or something similar. Once you've determined which module contains your macro code, just add that name before the "Execute_Save" in your code. Again, it should look like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Range("BF5").Value <> "" Then

Call Module1.Execute_save

End If

End Sub

 

[rjplante]

It works, but now it executes every time another cell is selected. What can I do to make sure it is only run once after cell BF5 is populated. This could also be linked to when cell X7 is populated.

 

[Sheltonite]

Are you manually changing the contents or are you wanting this to run whenever the value changes - for example, if you have a formula in BF5 and the value changes?

 

[rjplante]

There is a formula in BF5 and it is blank until M5 and X7 contain data. Once data is entered into both of these cells, BF5 is filled with data. It is then that I would like to have the data filled.

 

[Sheltonite]

It sounds like you're need this macro to evaluate the value of BF5 all the time until it is no longer blank and then run the code and then not run any more, Right? Would it be reasonable to link the macro to a command button you press to evaluate these when you tell it to? This would keep the constant running of the macro from happening.

Sorry, I don't have much else. Without some interaction from the user, I'm not sure how to do this without having something running constantly in the background to check if the criteria is met.

 

[RoryA]

It would make more sense to use a Change event and monitor the two input cells, I think.

Private Sub Worksheet_Change(ByVal Target As Range)
If not Intersect(Target, Range("M5,X7")) Is Nothing then
   If Range("BF5").Value <> "" Then Call Module1.Execute_save
End If

End Sub

FYI, you should only need the module name in the call statement if it's the same as the name of the macro you are calling (not good practice), or you have more than one routine with the same name, or the routine is actually in an object module - such as a Worksheet/ThisWorkbook/Userform.