vbCancel to exit Sub

[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.

 

[Bob Phillips]

--

HTH

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

 

[Bob Phillips]

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)

 

[Dennis]

Gee, it all seems so obvious when you see the answer!

Thanks, Dennis

 

[Jim Cone]

Bob,
I believe the InputBox function returns a zero length string ,"", if the Cancel
button is clicked.
Regards,
Jim Cone
San Francisco, CA

 

[Dennis]

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

 

[Bob Phillips]

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)

 

[Norman Harker]

Hi Bob!

" looking out across Poole Harbour to the Purbecks"

Must be foggy there!