Workbook_SheetChange, continues to loop with a Case Statement

  • Hello,


    I hope I am posting this in the right area.


    I am using Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) and a Case Statement that looks for a cell change, that calls a macro based on the value.
    I originally started out with the MsgBox Function to get all the case statements setup, to later add the macro's. It worked fine until I added the first macro. :/
    It calls the macro fine, but it keeps repeating the If statement: MsgBox("Email the Buyer?", vbYesNo) , until I select No. Otherwise each time I select yes, it runs the macro over and over again until I select No.


    All the MsgBox functions work fine and i thought it would be easy enough to just go back through and replace the MsgBox Okay Then!, with a Call statement to a Macro.


    Can someone please show me the error of my ways?


    Thanking you in advance for your time.

  • Try this macro although I tried your original and it worked properly for me.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Thanks Mumps, But nothing runs at all when I add the code. I have attached the file if that helps. You will need to select the drop down in cell CC226 on the "New Master Sheet" tab and select "Email the Buyer" to see what happens, all the other Drop down items seem to work with the MsgBox functions. Also the code is in the ThisWorkbook module (but you probably already knew that).
    Thanks for your help.

  • Place your code between the EnableEvents lines like this:


    Code
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
           Application.EnableEvents = False
          'Your code here
          Application.EnableEvents = True
    End Sub


    This should stop the macro from returning to the beginning of the code to run it repeatedly.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • I'm sorry,


    When I add the code as suggested nothing runs. Here is the code placed as suggested(unless I'm missing something).

  • My pleasure. :)

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Okay, that took care of the macro returning to the beginning of the code on that one, now I have a MsgBox that does not stop the code from running when "No" is selected. Is this the same problem?


    So the buyer general email calls the CopySaleInfoToBuyerEmailsSheet() macor which shows a MsgBox that allows the user to abort the rest of the code. However when the user selects "No" the code runs anyway?
    (This mesage box used to work).


    Am I running into the same problem?


    Please advise.


    Thank you.




  • Since the buyer general email calls the CopySaleInfoToBuyerEmailsSheet macro, when you answer "No" to the prompt, you exit the CopySaleInfoToBuyerEmailsSheet macro and return to the next line of code in the BuyerEmailsGeneral macro and it continues to run from there. If you eliminate the CopySaleInfoToBuyerEmailsSheet macro and combine it with the BuyerEmailsGeneral macro, then when you answer "No" the BuyerEmailsGeneral macro will be exited and the rest of the code will not continue to run. I hope this makes sense. It would look like this:


    You would have to do the same with the other BuyerEmails (1 to 10) macros.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • You are very welcome. :)

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!