Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Ventura, California, USA
    Thanked 0 Times in 0 Posts

    Input box Issue (Excel 2002)

    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.


  2. #2
    5 Star Lounger
    Join Date
    Jul 2002
    Toronto, Ontario, Canada
    Thanked 0 Times in 0 Posts

    Re: Input box Issue (Excel 2002)


    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")

    <font color=448800>'If the response is "" then the cancel button was pressed
    ' or nothing was entered</font color=448800>
    If strResponse <> "" Then
    <font color=448800>'Your File Save Code Goes Here</font color=448800>
    End If
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Brussels, Brussel, Belgium
    Thanked 0 Times in 0 Posts

    Re: Input box Issue (Excel 2002)

    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.

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Springfield, Ohio, USA
    Thanked 1 Time in 1 Post

    Re: Input box Issue (Excel 2002)

    As Pieter said, Application.InputBox returns False when you press Cancel. Here is a code snippit showing one possible way to deal with it:
    <pre>Dim myDate As Variant
    Dim myFile As String
    Dim myTitle As String
    myTitle = "CSV File"
    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</pre>

    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
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts