+ Reply to Thread
Results 1 to 6 of 6

Inputbox - formatted as Date, unable to exit sub on Cancel

  1. #1
    Registered User
    Join Date
    03-19-2013
    Location
    Spokane, WA
    MS-Off Ver
    Excel 2003
    Posts
    2

    Inputbox - formatted as Date, unable to exit sub on Cancel

    Hello,

    I am running a macro that asks a user to input a date to parse some data from a file folder. The issue I'm having is keeping an error from popping up (Run-time error '13': Type mismatch) when the user selects "Cancel" or "X"s out of the inputbox. Here is the pertinent code.

    Please Login or Register  to view this content.
    I have also tried the following codes to prevent the error.

    Please Login or Register  to view this content.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,008

    Re: Inputbox - formatted as Date, unable to exit sub on Cancel

    Try something like this...

    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Inputbox - formatted as Date, unable to exit sub on Cancel

    Hello Lewis,

    You declared eDate as a Date Type. The Cancel button returns a Boolean type. To fix the problem, change eDate to a Variant Type.
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  4. #4
    Registered User
    Join Date
    03-19-2013
    Location
    Spokane, WA
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Inputbox - formatted as Date, unable to exit sub on Cancel

    Thank you both for your quick responses.

    I found a solution and was going to delete this thread, but since there are solutions posted, hopefully this will help another.

    Here is the solution that I found so that I could ultimately check the date against another date in a different format later on in the VBA programming.

    Please Login or Register  to view this content.

  5. #5
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Inputbox - formatted as Date, unable to exit sub on Cancel

    Hello Lewis,

    FYI, using a Variant instead of string, eliminates the need to convert the string to a date. The Variant type does this for you.

  6. #6
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,008

    Re: Inputbox - formatted as Date, unable to exit sub on Cancel

    Quote Originally Posted by Leith Ross View Post
    FYI, using a Variant instead of string, eliminates the need to convert the string to a date. The Variant type does this for you.
    I would respectfully disagree. The Variant from the Input box is still a string and couldn't be compared directly to a serial date without converting it to a serial date.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Exit Sub on InputBox Cancel
    By qwerty7 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-01-2015, 07:33 PM
  2. [SOLVED] If Cancel on Input Box, exit sub
    By guitarsweety in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 07-28-2014, 07:24 PM
  3. When Cancel is selected, I want to exit sub
    By Rubert in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-20-2014, 05:02 PM
  4. [SOLVED] Input Box - Exit Sub on Cancel
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-04-2013, 11:32 AM
  5. tidy way to exit sub on cancel
    By tryer in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-19-2009, 04:44 AM
  6. Exit Sub when I hit cancel
    By erock24 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-20-2007, 01:27 PM
  7. Cancel an application exit from VBA?
    By helmekki in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-27-2005, 10:31 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1