Run a Macro from a cell value becoming "1"

Posted by Bob Earl on January 31, 2002 10:54 PM

I am trying to run a macro to import external data to a predetermined range of cells whenever a specific cell changes from a "0" to "1". The specific cell is tied through DDE to another external data source.
I have the cell DDE set up and running. I have the macro written and it currently runs when I pick a box.
I just need to find a way to trigger the macro from the specific cell.
Please help if you can.
Thanks

Posted by Tom Urtis on January 31, 2002 11:24 PM

I could be mistaken with an assumption here, but if the cell value changes to a "1", whether or not the "1" would be replacing a "0", you might probably still want the macro to run. This code should do that. If you need to have the macro run only when a "0" is replaced by a "1" then please repost. But this code here should allow you to be more flexible in that the macro will run anytime a "1" is entered (except by formula), including a text 1, such as preceded by an apostrophe.

Right click on the sheet tab, left click on View Code, and paste this in.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, [A1]) Is Nothing And Target = 1 Then
Run "Macro1"
End If
End Sub

Replace "Macro1" with the name of your actual macro.

Any help?

Tom Urtis

Posted by Bob Earl on February 03, 2002 10:57 PM

Thanks for the posting Tom. This looks to me like it just may do the trick. I will give it a try Monday morning when I get back to work.
I appreciate the assistance.
Your assumption looks to be correct. The data the cell will be getting will only be a "0" or a "1". I need the Macro to run when the bit changes to a "1". I will have to play with it a bit. I only want the macro to run one time when the cell becomes a "1" then wait for it to become a "1" again before it runs again. Over and Over again.