Code mysteriously failing to initiate after selecting the "Cancel" option in a message box.

[bellg]

I have one subroutine that calls many other subroutines based on a number of buttons/conditions I've set up in my Excel worksheet. Below is a truncated example of this.

Public a as Integer
Public ans as Integer

Public Sub StartingMacro()

   If Application.Caller = "button1" Then Call Macro1
   If Application.Caller = "button2" Then Call Macro2
   If Application.Caller = "button2" Then Call Macro3
   If a = 1 Then Call Macro6
   If a = 2 Then Call Macro7
   If a <> 3 Then Call Macro8

End Sub

I have this line of code in many of my Macros that get called by "StartingMacro":

ans = MsgBox("Are you sure you want to do something?", vbYesNoCancel)
If ans <> vbYes Then Exit Sub

When the user selects "No" or "Cancel", assigning ans = vbNo or ans = vbCancel, it exits whatever Macro it's in and finishes through the "StartingMacro". I can step through my code and I can see it do this without a problem. However, in the instance where I select "Cancel", the buttons in my worksheet no longer call macros after the "StartingMacro" subroutine completes through to the end. It completely finishes, but it's like the code is in debug mode or something without showing it in the VB Editor window. I literally have to go into the VB Editor and hit the square "Reset" button if I ever select "Cancel" to a message box. Remember, this is after I step through the code until the "End Sub" line without issues. The most curious part is in the instance when I select "No" because this phenomenon doesn't happen at all. The code finishes through the "StartingMacro", and I can select other buttons in the worksheet having them call other Macros without a problem. The only difference is answering "No" or "Cancel".

Does anyone know why that happens?

NOTE: I know I can create a message box with only Yes/No, but I want all three options. I also know that if I change the "ans" variable to "ansOther" (or some other name) then the "Cancel" option works just fine. However, I use the "ans" variable everywhere in my code (it is lengthy) to simplify the number of variables I have to declare/use, so declaring a custom variable every time I use a message box sounds dreadful. I'm really looking for an answer to the difference between the user selecting "No" and "Cancel" in relation to my problem.

DOES ANYONE KNOW WHY THIS HAPPENS?

 

[Anthony47]

You are using "Public" variable: keep in mind that they retain any set value until a new value is set, or the file is closed, or the square "Reset" is pressed.

I found the problem - thanks for the debugger idea. Public variables do not reset automatically when the code finishes execution, so the "ans" variable was not resetting to 0

Eh eh ...

Variable defined at "Module" level (be they Public or private) are designed with that behaviour. It is good practice keeping to a minimum this type of variable.
See the vba help online, "Validity and visibility of variable" and "Variable life time"

As Fluff already suggested, you can create a Sub that clears all the Public or anyway the variable defined on top of Modules; something like

Sub ClearAll()
Variable1 = Empty        
Variable2 = Empty
'etc
End Sub

This assume that Variable1, Variable2 etc are Public or defined on top of Modules
Then you will use

Call ClearAll

Bye