vbCancel to exit Sub

  • Thread starter Thread starter Dennis
  • Start date Start date
D

Dennis

I must be looking up answers the wrong way.

Sub ..........

Do Until UCase(MyView) = "ALL" Or UCase(MyView) = "CMI" Or UCase(MyView) = "XL"
MyView = InputBox("Enter CMI or XL or ALL", "Enter Desired View")
If vbCancel Then Exit Sub
Loop
..............
.............
End Sub

I want to Exit the Sub if the Cancel Button is pressed.

vbCancel has a value of 2 as soon as I step into the first Sub GetMyView()

How do I reset vbCancel to 0 just before the message box?

Or, how do I exit the Sub (out of the loop) only when the Cancel Button is pressed
but continue the Sub when CMI or XL or ALL is entered?

I know this is basic.
 
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Dennis,

You can't set vbCancel as it is a VBA constant,

Test MyView against vbCancel

Sub ..........

Do Until UCase(MyView) = "ALL" Or UCase(MyView) = "CMI" Or UCase(MyView)
= "XL"
MyView = InputBox("Enter CMI or XL or ALL", "Enter Desired View")
If MyView = vbCancel Then Exit Sub
Loop
..............
.............
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob,
I believe the InputBox function returns a zero length string ,"", if the Cancel
button is clicked.
Regards,
Jim Cone
San Francisco, CA
 
Woops!

I got a Type mismatch with "If MyView = vbCancel Then Exit Sub"

MyView = null and vbCancel =2

So I attempted:

If Val(MyView) = vbCancel Then Exit Sub
Then I could not exit the Sub by pressing Cancel

Thoughts?

Dennis
 
Sorry, my mistake. As Jim says, you need to test the result for empty

Sub ..........

Do Until UCase(MyView) = "ALL" Or UCase(MyView) = "CMI" Or UCase(MyView)
= "XL"
MyView = InputBox("Enter CMI or XL or ALL", "Enter Desired View")
If MyView = "" Then Exit Sub
Loop
..............
.............
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top