+ Reply to Thread
Results 1 to 6 of 6

Input box cancel produces error

  1. #1
    pkeegs
    Guest

    Input box cancel produces error

    I have the following to insert a date into the active cell. It works all
    right to enter the default or a new date with OK, but when Cancel is clicked
    it only produces an error. What do I need to exit the sub.

    ActiveCell.Value = CDate(InputBox("Enter the end date of the period.", _
    strTitle, Range("BalanceDate").Value))

    Regards

  2. #2
    Tom Ogilvy
    Guest

    Re: Input box cancel produces error

    Dim s as String
    s = InputBox("Enter the end date of the period.", _
    strTitle, Range("BalanceDate").Value)
    if s <> "" then
    if isdate(s) then
    ActiveCell.Value = cdate(s)
    else
    Msgbox "Bad data"
    end if
    end if

    --
    Regards,
    Tom Ogilvy

    "pkeegs" <pkeegs@discussions.microsoft.com> wrote in message
    news:60C4B82F-B7AC-461A-AC9B-AEB035FD7220@microsoft.com...
    > I have the following to insert a date into the active cell. It works all
    > right to enter the default or a new date with OK, but when Cancel is

    clicked
    > it only produces an error. What do I need to exit the sub.
    >
    > ActiveCell.Value = CDate(InputBox("Enter the end date of the period.", _
    > strTitle, Range("BalanceDate").Value))
    >
    > Regards




  3. #3
    Rick R.
    Guest

    RE: Input box cancel produces error

    Try using vbYes and vbNo. If youg get vbNo, then exit sub



    "pkeegs" wrote:

    > I have the following to insert a date into the active cell. It works all
    > right to enter the default or a new date with OK, but when Cancel is clicked
    > it only produces an error. What do I need to exit the sub.
    >
    > ActiveCell.Value = CDate(InputBox("Enter the end date of the period.", _
    > strTitle, Range("BalanceDate").Value))
    >
    > Regards


  4. #4
    pkeegs
    Guest

    Re: Input box cancel produces error

    Thanks Tom
    I copied your code into a test macro but when I ran it, it stopped at
    "s=InputBox...." Should there be a reference to the cell where value is
    being inserted?

    "Tom Ogilvy" wrote:

    > Dim s as String
    > s = InputBox("Enter the end date of the period.", _
    > strTitle, Range("BalanceDate").Value)
    > if s <> "" then
    > if isdate(s) then
    > ActiveCell.Value = cdate(s)
    > else
    > Msgbox "Bad data"
    > end if
    > end if
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    > "pkeegs" <pkeegs@discussions.microsoft.com> wrote in message
    > news:60C4B82F-B7AC-461A-AC9B-AEB035FD7220@microsoft.com...
    > > I have the following to insert a date into the active cell. It works all
    > > right to enter the default or a new date with OK, but when Cancel is

    > clicked
    > > it only produces an error. What do I need to exit the sub.
    > >
    > > ActiveCell.Value = CDate(InputBox("Enter the end date of the period.", _
    > > strTitle, Range("BalanceDate").Value))
    > >
    > > Regards

    >
    >
    >


  5. #5
    Dave Peterson
    Guest

    Re: Input box cancel produces error

    Tom does that later with activecell.value.

    Are you sure that there is a single cell named BalanceDate?



    pkeegs wrote:
    >
    > Thanks Tom
    > I copied your code into a test macro but when I ran it, it stopped at
    > "s=InputBox...." Should there be a reference to the cell where value is
    > being inserted?
    >
    > "Tom Ogilvy" wrote:
    >
    > > Dim s as String
    > > s = InputBox("Enter the end date of the period.", _
    > > strTitle, Range("BalanceDate").Value)
    > > if s <> "" then
    > > if isdate(s) then
    > > ActiveCell.Value = cdate(s)
    > > else
    > > Msgbox "Bad data"
    > > end if
    > > end if
    > >
    > > --
    > > Regards,
    > > Tom Ogilvy
    > >
    > > "pkeegs" <pkeegs@discussions.microsoft.com> wrote in message
    > > news:60C4B82F-B7AC-461A-AC9B-AEB035FD7220@microsoft.com...
    > > > I have the following to insert a date into the active cell. It works all
    > > > right to enter the default or a new date with OK, but when Cancel is

    > > clicked
    > > > it only produces an error. What do I need to exit the sub.
    > > >
    > > > ActiveCell.Value = CDate(InputBox("Enter the end date of the period.", _
    > > > strTitle, Range("BalanceDate").Value))
    > > >
    > > > Regards

    > >
    > >
    > >


    --

    Dave Peterson

  6. #6
    pkeegs
    Guest

    Re: Input box cancel produces error

    Hi Dave & Tom,
    message to all us amateurs "When you set up a test sheet to test code that
    is proposed, don't forget to define the names that have been used!!!" It
    works well.

    Thanks very much

    Regards

    "Dave Peterson" wrote:

    > Tom does that later with activecell.value.
    >
    > Are you sure that there is a single cell named BalanceDate?
    >
    >
    >
    > pkeegs wrote:
    > >
    > > Thanks Tom
    > > I copied your code into a test macro but when I ran it, it stopped at
    > > "s=InputBox...." Should there be a reference to the cell where value is
    > > being inserted?
    > >
    > > "Tom Ogilvy" wrote:
    > >
    > > > Dim s as String
    > > > s = InputBox("Enter the end date of the period.", _
    > > > strTitle, Range("BalanceDate").Value)
    > > > if s <> "" then
    > > > if isdate(s) then
    > > > ActiveCell.Value = cdate(s)
    > > > else
    > > > Msgbox "Bad data"
    > > > end if
    > > > end if
    > > >
    > > > --
    > > > Regards,
    > > > Tom Ogilvy
    > > >
    > > > "pkeegs" <pkeegs@discussions.microsoft.com> wrote in message
    > > > news:60C4B82F-B7AC-461A-AC9B-AEB035FD7220@microsoft.com...
    > > > > I have the following to insert a date into the active cell. It works all
    > > > > right to enter the default or a new date with OK, but when Cancel is
    > > > clicked
    > > > > it only produces an error. What do I need to exit the sub.
    > > > >
    > > > > ActiveCell.Value = CDate(InputBox("Enter the end date of the period.", _
    > > > > strTitle, Range("BalanceDate").Value))
    > > > >
    > > > > Regards
    > > >
    > > >
    > > >

    >
    > --
    >
    > Dave Peterson
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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