Run macro only once based on cell value

[cmb80]

Hi there, I currently have a macro run if cell E2 has the text "In"

If cell E2 has any other word / value other than "In" - the macro shouldn't run.

How can I get the macro to only run once (ie the first time the cell E2 changes to "In") and then stop?

Currently the macro just loops and I can find no way to stop this.

Appreciate any help on this once

[TMS]

Define a global public Boolean variable. The default will be FALSE. When your macro runs, test for TRUE and, if it is, exit. If it isn't, set it to TRUE and continue with the macro.


Regards, TMS

[pjwhitfield]

Use another cell to hold a flag to say the macro has been ran and in your macro code check that flag, if false run the code and set the flag to true

[cmb80]

Define a global public Boolean variable. The default will be FALSE. When your macro runs, test for TRUE and, if it is, exit. If it isn't, set it to TRUE and continue with the macro.


Regards, TMS

Sorry, lost me on that one

[pjwhitfield]

Trevors is the better option as I missed the bit around looping however if you want to have this facility across days/ openings of the workbook then you'll need to save the value in the workbook.

[cmb80]

Use another cell to hold a flag to say the macro has been ran and in your macro code check that flag, if false run the code and set the flag to true

I tried that by marking an "X" in cell F2 - but the looping still occured

[cmb80]

This is my current code

Please Login or Register  to view this content.

[pjwhitfield]

Sorry, lost me on that one

In a module

add the following line at the top

Please Login or Register  to view this content.

in your code

Please Login or Register  to view this content.

[Loganeb]

Here's a code that only runs a) if E2 is the cell changed, and b) only if the value is changed to "In"

Please Login or Register  to view this content.

[Loganeb]

Sorry, duplicate post

Note: This macro will run EVERY time the cell is changed to "In" If you only want it once ever (per use of the file), then use this:

Please Login or Register  to view this content.

[TMS]

Of course, if E2 has the text "In" in it, change it to something else ... like "Done"

It needs to be clarified if this is once per session or once only (for ever).

It'd help if you shared your code.

Regards, TMS

[cmb80]

The worksheet is a constant feed from the internet - Cell E2 will either say "In" or "Not".

I would like the macro to run on the first occasion the cell E2 changes to "In" - once it changes to "In" it wont change back to "Not" - but it will keep refreshing every 5 seconds, and hence causing my macro to keep being called

[Loganeb]

cmb80,

Do you want the macro to work once per each time you open the file? So, if you were to open it, and change the value of E2 to "In", it would run once, then not run again for the rest of the time you're working with the file in that sitting? My code above will accomplish that. Or do you want it to run once, then basically delete itself from your workbook?

Place that code into the sheet code (not a module) and it should work.

[xladept]

The first time you run it have it post the current date in another cell. Then, subsequently, you can test that cell for the days date and, if the current date is greater than the posted date let the macro run.

[cmb80]

thanks for your help guys, I think date stamping is possibly the best solution

[TMS]

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[xladept]

You're welcome!