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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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

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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
>