run macro after cell changes

rjplante

Active Member
Joined
Oct 31, 2008
Messages
489
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.


Rich (BB code):
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

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

End Sub
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Sheltonite

New Member
Joined
Jan 23, 2014
Messages
22
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
 
Last edited:

rjplante

Active Member
Joined
Oct 31, 2008
Messages
489
My save macro is titled Execute_save. I am not sure what a module is.
 

Sheltonite

New Member
Joined
Jan 23, 2014
Messages
22
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

Active Member
Joined
Oct 31, 2008
Messages
489

ADVERTISEMENT

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

New Member
Joined
Jan 23, 2014
Messages
22
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

Active Member
Joined
Oct 31, 2008
Messages
489

ADVERTISEMENT

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

New Member
Joined
Jan 23, 2014
Messages
22
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,096
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
It would make more sense to use a Change event and monitor the two input cells, I think.
Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,822
Messages
5,598,304
Members
414,224
Latest member
Crazy_FC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top