Cancel Button on Input Box

[AZGJC]

I'm new to VBA programming, and I'm having trouble with getting the input box to close when the cancel button is clicked. If anyone has any suggestions, it would be much appreciated.

Thanks,
Garrett


Message = "Input the date which you want to update. The format must be YYYYMMDD."
TitleBarTxt = "Resource Planner"
With Application.FileSearch
Do
uResponse = Application.InputBox(Message, TitleBarTxt)
.LookIn = "Z:\Nav\Departments\"
.Filename = uResponse & "*.xls"
If StrPtr(uResponse) = 0 Then
Exit Sub
End If
Loop Until .Execute > 0
End With
Workbooks.Open "I:\Nolan_Nav\Departments\" & uResponse
Workbooks.Open "I:\Nolan_Nav\Departments\" & uResponse & "Scorecard.xls

 

[AZGJC]

Obviously I mistyped, the directory in the last two commands, so disregard that. Thanks.

 

[PHV]

Dim uResponse As Variant
...
uResponse = Application.InputBox(Message, TitleBarTxt)
If uResponse = False Then Exit Sub 'Cancel clicked
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[HughLerwill]

Az..

I see you are using StrPtr to determine if the Cancel button really has been pressed. That works in Vb6 and I've just tested it under Excel 2003 SP2 no problem. However, have you tried using the conventional;

If uResponse = "" Then

I believe StrPtr is an unsupported feature of the language (possibly because it has some quirks)

regards Hugh,

 

[HughLerwill]

Further to PHV's

I was diming my uResponse to a string before using it and it worked too.

Hugh,

 

[AZGJC]

Thanks for the help. I was close with everything I tried, but I couldn't quite get it.

 

[HughLerwill]

AZ,

Ah so you were orinally using uResponse dimmed (or default dimmed) as a Variant, so as PHV has pointed out you should test for False.
If you had dimmed uResponse to a String the the StrPtr approach would have worked.

Always more than one way.

regards Hugh

 

[AZGJC]

It is dimmed as a string, but it still wouldn't cancel out when clicked.