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]

Honestly I was not able to understand which problem you are experiencing, but let me try giving some generic ideas...

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. Also, a "Public" declaration in a Class module is not visible outside the Class module ("class module" is for example the module that contains worksheet events macro, or ThisWorkbook); in other words a "Public" declaration must be done at the beginning of a standard module. Also, any further declaration of the same name of variable at Subroutine level override the "Public" variable (ie that subroutine will access its declared variable, not the Public one).

So check that Public declaration occours only once and on top of Standard vba Modules; check that the same name is not declared locally in any subroutine; check that Public variables are always set by a Subroutine, if their value has to be used by the code that will be executed "after" (or keep in mind that they will retain the last set value).

If nothing of these points will "switch on a light" then please describe again the problem with more detailed wording.

Bye

 

[bellg]

Thanks for the reply Anthony.

I only have standard Modules in my project. I've also only declared the public variable "ans" once in my project, and nowhere else. My public variables are always set by a subroutine.
I do use the "ans" variable a lot in my code. It's meant to fulfill temporary needs. If I want to ever save the "ans" value to something more lasting, I change the variable name.

Let me try to re-explain. All buttons in my worksheet are linked to the "StartingMacro" subroutine. Depending on which button that gets pushed, a different macro is called. Let's say I push "button2" in my below example:

"StartingMacro" will be initiated. Then, "Macro2" will be called. Within Macro2, there's a message box that asks "Do you want to do something?" with Yes, No, and Cancel as options. When I select "Cancel", it exits Macro2 through the use of the Exit Sub command. Note here, if I selected"No" it would also exit Macro2 through the Exit Sub command. The code returns to the StartingMacro subroutine to finish execution. It does this without a problem.

At this point, all code as finished execution.

1) In the case where I selected "No", I can try to click another button in the worksheet. Let's say I click "button1", and the "StartingMacro" subroutine is initiated again. This time, "Macro1" will be called and the code will execute without a hiccup.
2) In the case where I selected "Cancel", I can try to click another button in the worksheet, but nothing happens. The "StartingMacro" subroutine isn't initiated. In fact, no buttons in my worksheet will initiate any code. I have to physically go into the VB Editor window and push the square "Reset" button before my command buttons will work again.

The only difference is selecting "Cancel" or "No" in the previous macro. I hope this is a better explanation. Let me know if I can clarify anything, and thanks again for the reply.

 

[Anthony47]

Let's collect some debug information.
1) Modify StartingMacro as follows:

Public Sub StartingMacro()
Debug.Print 1, Format(Now, "hh:mm:ss"), Application.Caller, "ans=" & ans, "A=" & a
   If Application.Caller = "button1" Then Call cipp
   If Application.Caller = "button2" Then Call cipp
   If Application.Caller = "button2" Then Call cipp
Debug.Print 11, Format(Now, "hh:mm:ss"), Application.Caller, "ans=" & ans, "A=" & a
   If a = 1 Then Call cipp
   If a = 2 Then Call cipp
   If a <> 3 Then Call cipp
Debug.Print 111, Format(Now, "hh:mm:ss"), "ans=" & ans, "A=" & a 
End Sub

Then modify the handling of some of the Macros; for example Macro1, Macro2 and Macro3, by adding debug instructions:

Debug.Print 3, Format(Now, "hh:mm:ss"), "NameOfTheMacro", "ans=" & ans, "A=" & a
ans = MsgBox("Are you sure you want to do something?", vbYesNoCancel)
Debug.Print 4, Format(Now, "hh:mm:ss"), "NameOfTheMacro", "ans=" & ans, "A=" & a
If ans <> vbYes Then Exit Sub
Debug.Print 41, Format(Now, "hh:mm:ss"), "NameOfTheMacro", "ans=" & ans, "A=" & a

Replace "NameOfTheMacro" with the real name of the macro

At this point:
-press Button1
-answer No to the question
-press Button2
-answer Cancel to the question
-press Button3
From what you said there will be no any msgbox and no macro will be executed
-press Button1
From what you said there will be no any msgbox and no macro will be executed
-press the square "reset" button
-press Button3
-answer No to the question
It is better, even if you follow the above steps, that you write down what you execute and insert this log into your next message.

At this point go to the vba window, and press Contr-g to open the "Immediate" window.
Copy all the content of the Immediate window and insert it into your next message; specify which is the real name of the pressed buttons and the real name of the macro that are to be executed when the buttons are pressed.

Specify also wich type of "buttons" you use (Module? ActiveX? Shape?), the name of the modules that host the code of Sub StartingMacro and the macros that are executed /should have been excecuted when pressing the Buttons (only the 3 buttons used in this test); you read this information on top of the vba window, typically it reads "Microsoft Visual Basic, Application Edition - NameOfTheWorkbook.xlsm - [NameOfTheModule (Code)]"

Bye

 

[Anthony47]

I am sorry, but I suggested the wrong code:
-all of the several "Call cipp" (that I used for testing the code) has to be replaced by your real calls; eg
Call Macro1
Call Macro2
and so on...

Bye

 

[bellg]

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.

I had this line of code (not in the original message - my bad) that forced the macro to exit PRIOR to reaching the Application.Caller lines:

If ans = vbCancel Then Exit Sub

This can be seen when I ran the debugger after resetting. I selected Cancel on the first run:
1 10:48:09 butArchive ans=0 A=0
3 10:48:10 AllButtons ans=0 A=0
4 10:48:15 AllButtons ans=2 A=0
11 10:48:15 butArchive ans=2 A=0
111 10:48:15 ans=2 A=0
1 10:48:21 butArchive ans=2 A=0
1 10:48:31 butArchive ans=2 A=0
1 10:48:32 butArchive ans=2 A=0

Note differences in my real code:
AllButtons = StartingMacro
butArchive = Macro3 (or whichever macro example, doesn't really matter)

I fixed the problem by throwing "ans = 0" at the beginning of StartingMacro, something I typically do when I declare certain variables, but this one must have gotten lost since I used it so much.

   ans = 0
   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

 

[bellg]

Another solution to this problem would be to reset ALL variables at the start of the StartingMacro, and not just the "ans" variable.

Is there an way to "click" the Reset button with VBA so that I know that all of my public variables have been reset?

 

[Fluff]

One way would be to run this first

Sub ClearAll()
End
End Sub

 

[bellg]

The End command would end the code if I put it at the start, which wouldn't be good. I can't put it at the end either as I have some "Exit Sub" commands that wouldn't allow the code to reach that line.

However, if I replaced all of my "Exit Sub" commands with "Call ClearAll" commands, that might work. Thanks for the suggestion as I might end up doing that.

Still, is there a way to reset variables without ending the code altogether?

 

[Fluff]

Still, is there a way to reset variables without ending the code altogether?

You could possibly call a sub that simply clears them one by one.