How to stop simultaneously running macros - run macro 1, then macro 2

**sighlent1** · 05-23-2011, 02:56 PM

Hey,

I have this workbook that has 2 macros that are running simultaneously and I'm looking to run macro 1, then macro 2

The one macro will run if there are any changes made to the worksheet (a message will pop up if the sum of values (=A1+B1; =A2+B2;...) in a column (C) is greater than 0)

Another macro will change the original values (in column B)

While running the 2nd macro, value B1 will change, then the 1st macro will run saying, "Values in C2, C5, C6 are greater than zero". After pushing "OK", the 2nd macro will continue and fill in the value for B2, then the 1st macro will run again, saying "Values in C5, C6 are greater than zero"......

Is there a way to have the 2nd macro run fully through it's cycle, and then have the 1st macro run afterwards???

Thank you and I appreciate your time and efforts,
Bob

**TMS** · 05-23-2011, 03:01 PM

It sounds as though you have a Worksheet Change event monitoring some/all the cells. This fires when the second macro changes a cell.

You can use:

Please Login or Register  to view this content.

in Macro 2.

However, you might miss out on some of the reporting the WSCE gives you.

As you haven't posted any of the code or the workbook, it's difficult to give more than an indication of what is happening.

Regards

**sighlent1** · 05-25-2011, 11:09 AM

Thank you for your response TMShucks

I created a workbook that very closely reacts the same way as the project that I'm working on

I like the idea that the messages pop up when a change is made, but when running the macro, the messages pop up while the macro runs and the 2 macros are "interacting" with each other. I'd like to see the macro run it's course, then afterwards the messages to pop up

Could you help me out here?

Thank you sooo much,
Bob

**TMS** · 05-25-2011, 11:36 AM

Get rid of the Worksheet Change event and replace Macro 1 with the following code:

Please Login or Register  to view this content.

Regards

**sighlent1** · 05-25-2011, 03:24 PM

I'm so sorry for being such a pain in the neck, but I think this time I created exactly what I'm looking for

please see the attachment

Thank you soooo much,
Bob

**TMS** · 05-25-2011, 05:03 PM

I don't think the two examples are that different ... it might have been worth your while trying to adapt the code from your style to mine. It only required changing some of the ranges, nothing very adventurous.

Anyway, amended code and updated spreadsheet attached.

Please Login or Register  to view this content.

Regards

**sighlent1** · 05-26-2011, 09:48 AM

This looks excellent

But what I'd also like to see happen is if G9, G12, G19, or G22 value is deleted or changed so that the values in the I Column change from 0 to Greater Than 0, the messages will pop up. I'm not sure how to do this without the "Change" event

Thank you soo much and I highly appreciate your time,
Bob

**TMS** · 05-26-2011, 01:41 PM

When you say " if G9, G12, G19, or G22 value is deleted or changed ..." do you actually mean those specific cells or do you mean any one of the cells in the range G9 to G22.

It is possible. You need to consider the two scenarios separately. On the one hand, you're running a macro that sets several values and then checks them all to see if they meet a criterion ... no event handling because it gets in the way. On the other hand, you're making individual changes and want a prompt for that cell ... not the whole range because you're not changing the whole range ... so you can use a worksheet change event.

I'll have a look at it but it's important that you answer the first question.

Regards

**sighlent1** · 05-26-2011, 01:54 PM

Yes, specifically those cells for the workbook that I attached in the last message

The only thing that I physically changed in the one that you gave me and this one is the value in Cell D4 from 50 to 90

Thank you so much

Bob

**TMS** · 05-26-2011, 04:08 PM

Have a look at the updated workbook.

I think this does what you have asked for.

Regards

**sighlent1** · 05-26-2011, 04:57 PM

This is looking Excellent!!!

Is there a way to have 2 pop up messages if both G12 & G19 were deleted? I just get an error message if I do that

**TMS** · 05-26-2011, 05:22 PM

What error message? How are you deleting the two cells?

If I select G12 and Ctrl-Select G19 and delete them, I just get one cell flagged but no error.

**TMS** · 05-26-2011, 05:25 PM

Change the Worksheet Change event to be:

Please Login or Register  to view this content.

Regards

**Skelvissie** · 05-26-2011, 06:24 PM

Hi There

I am nowhere near the expert that the forum contributors are but I found myself in a similar situation recently. My macros were just running straight after one another.
I just needed a simple function to stop them e.g. I used this:

Please Login or Register  to view this content.

You could probably use something else, in fact I don't even know if this would even work in your case but it works fine in my example.

Good luck anyway, I hope you get your issue solved soon

Cheers,
Skelvissie

**sighlent1** · 05-27-2011, 09:32 AM

TMShucks, That works PERFECTLY!!!!!!!!!

Thank you soooo much.....I can't express how much you've helped me out....I really appreciate it!!!
Bob

Skelvissie, Thank you sooo much as well!!!!!!!

**TMS** · 05-27-2011, 09:36 AM

You're welcome. Thanks for the rep.

**sighlent1** · 05-27-2011, 03:37 PM

Hey TMShucks,

I added a little bit more to this spreadsheet

"Macro 1" & "Macro 2" are working perfectly

If you delete any cell value in range G9:G22 (Multiplier 1) where the corresponding value in column L = 0, the pop up message comes up which is excellent. But I wasn't able to duplicate the process for range I9:I22 (Multiplier 2). I tried to edit the Change Event for Sheet 1, but came up with an error

Any idea how to fix this?

As always, Thank you,
Bob

**TMS** · 05-27-2011, 04:18 PM

Try:

Please Login or Register  to view this content.

Regards

**Skelvissie** · 05-29-2011, 05:24 PM

Hi Therem

I'm thrilled to hear that helped. I'm a novice myself but I find the simplest method of coding works the best for me, I understand it far easier.

But I'm glad this helped.

Have an awesome day.

Cheers,
Skelvissie

**sighlent1** · 05-31-2011, 09:00 AM

Excellent, Thank you SOOOO Much

That's PERFECT!!!!!!!!!

How to stop simultaneously running macros - run macro 1, then macro 2

LinkBack

Thread Tools

Rate This Thread

Display

How to stop simultaneously running macros - run macro 1, then macro 2

Re: How to stop simultaneously running macros - run macro 1, then macro 2

Re: How to stop simultaneously running macros - run macro 1, then macro 2

Re: How to stop simultaneously running macros - run macro 1, then macro 2

Re: How to stop simultaneously running macros - run macro 1, then macro 2

Re: How to stop simultaneously running macros - run macro 1, then macro 2

Re: How to stop simultaneously running macros - run macro 1, then macro 2

Re: How to stop simultaneously running macros - run macro 1, then macro 2

Re: How to stop simultaneously running macros - run macro 1, then macro 2

Re: How to stop simultaneously running macros - run macro 1, then macro 2

Re: How to stop simultaneously running macros - run macro 1, then macro 2

Re: How to stop simultaneously running macros - run macro 1, then macro 2

Re: How to stop simultaneously running macros - run macro 1, then macro 2

Re: How to stop simultaneously running macros - run macro 1, then macro 2

Re: How to stop simultaneously running macros - run macro 1, then macro 2

Re: How to stop simultaneously running macros - run macro 1, then macro 2

Re: How to stop simultaneously running macros - run macro 1, then macro 2

Re: How to stop simultaneously running macros - run macro 1, then macro 2

Re: How to stop simultaneously running macros - run macro 1, then macro 2

Re: How to stop simultaneously running macros - run macro 1, then macro 2

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions