Input box Issue (Excel 2002)

Topic

New Reply

I have written a pretty slick little application thanks to you folks here. But I have one more issue. At the end of my macro I have a input box for the user to put the name they want the file saved as in. Everything runs great except if you hit cancel. If you hit cancel it takes the user to the VBE.

Is there an error handling event I can add so it just stops the macro and bow up??

Thank yo uin advance.

Dan

Reply | Quote

Replies

Dan,

You don’t need to trap an error, since there isn’t one that occurs. You need to check for an empty string

strResponse = InputBox(“Prompt”)

‘If the response is “” then the cancel button was pressed
‘ or nothing was entered
If strResponse “” Then
‘Your File Save Code Goes Here
End If

Reply | Quote

hi Dan, Bryan,

in excel there are two types of inputboxes:
the inputbox function from the vba class (“vba.inputbox” or plainly “inputbox” as the vba library will allways take precedence over excel’s library)
the inputbox method from the application class. (application.inputbox)

the function behaves as Bryan describes, but the method returns false when cancel is clicked and not an empty string. to make sure you have the function, you could use vba.inputbox(…
unfortunately, if you look up online help (at least in excel 97) you get help for the inputbox function which doesn’t refer to the method (which does refer to the function in its see also link in the helpscreen.)
to read the help of both the function (which is actually also a method in oo-speak) & the method, bring up the object explorer with F2 in the VBE, search for inputbox and click on it in the resulting list. the press F1, repeat this procedure for the second result (first one is the vba class, the other is application)

greetings, pieter.

Reply | Quote

As Pieter said, Application.InputBox returns False when you press Cancel. Here is a code snippit showing one possible way to deal with it:

Dim myDate As Variant
Dim myFile As String
Dim myTitle As String
    myTitle = "CSV File"
    Do
        myDate = Application.InputBox("Enter Date", myTitle, Format(Date, "dd mmm yy"), , , , , 2)
        If VarType(myDate) = vbBoolean Then
            If Not myDate Then Exit Sub     ' Pressed cancel
        End If
        If Not IsDate(myDate) Then myTitle = "Invalid Date -- Try Again"
    Loop Until IsDate(myDate)
    myFile = Format(myDate, "yyyymmdd") & ".csv"
    Workbooks.Open Filename:="E:My Documents" & myFile

However, in your case, if you asking the user for a filename, it would be better to use Application.GetSaveAsFilename. Take a look at the help file for the parameters. HTH –Sam

Reply | Quote