How do I end/exit a macro?

Posted by Michael Robson on March 08, 2000 10:01 AM

I have a VBA Excel 97 macro that asks for two inputs and then makes a chart from those inputs. The input boxes that appear on the screen when running the macro have a CANCEL button. Problem is when the cancel button is pressed the macro assumes that the input is blank and moves to the next input box. What do I write that will cause the macro to end completely, without going to the next line of code. I have tied "If...Then Exit Sub", all that does is skip to the next line of code. I have also tried "If...Then End", same thing it skips to the next line of code.

Any help would be much appreciated, thanks =)

Michael Robson

Posted by Keith on March 08, 2000 10:26 AM

Here is some code right out of one of my macros:

Dim SomeValue as Integer
Dim Answer as Integer

' Determine if value has been entered
SomeValue = InputBox("Enter a Value", 0)
If SomeValue = 0 Then
Beep
Answer = MsgBox("Value is missing! Do you wish to continue ?", vbYesNo + vbQuestion + vbDefaultButton2 + vbSystemModal)
If Answer = vbNo Then
Exit Sub
End If
End If

Other code
. . .
. . .

Posted by Jaime on March 08, 2000 10:28 AM

Hi,

Exit sub works for me. I think maybe ur criteria at the If statement might not be working.
What does ur IF statement test for?

Posted by Michael Robson on March 08, 2000 10:37 AM

Here is one of the statemetns directly copied/pasted,

Firstname = Application.InputBox("Please enter the first name to search for.", Type:=1)
If IsEmpty(Firstname) Then End

I also tried this,

Firstname = Application.InputBox("Please enter the first name to search for.", Type:=1)
If IsEmpty(Firstname) Then Exit Sub

and this,

Firstname = Application.InputBox("Please enter the first name to search for.", Type:=1)
If IsEmpty(Firstname) Then Exit Sub
End If

and this

Firstname = Application.InputBox("Please enter the first name to search for.", Type:=1)
If Firstname="" Then Exit Sub

Every time I hit cancel it will just jump to the next line. There are three parts to this macro, the two input boxes for first and last name, then the code to make a graph. If cancel is pressed on the first input box it jumps to the next box. If cancel is pressed on the second input box it jumps straight to the graph making part. Of course since no input was taken it makes a blank graph.

This seems like adding If...Then Exit Sub would work perfectly, but it isn't so far.

Am I missing something completely obvious?

Thanks again =)

Michael Robson

Posted by Jaime on March 08, 2000 11:05 AM

Hi,

Try this

firstname = Application.InputBox(Prompt:="Please enter the first name to search for.")

If (firstname) = False Then Exit Sub

Posted by Jaime on March 08, 2000 11:09 AM

Hi,

Try this

firstname = Application.InputBox(Prompt:="Please enter the first name to search for.")

If (firstname) = False Then Exit Sub

I guess inputbox returns a boolean

Tell me if it works for u

Posted by Michael Robson on March 08, 2000 12:32 PM

Thanks Jaime!

That did the trick! Although I still am not sure why it wasn't working before I can at least rest knowing it does now. =)

Thanks again,
Michael Robson Hi, Try this firstname = Application.InputBox(Prompt:="Please enter the first name to search for.") If (firstname) = False Then Exit Sub

Posted by Jaime on March 08, 2000 12:45 PM

Yeah, In fact i have a textbook that codes it just like u did. But it does not seem to work that way. Go figure, MS