I would like to set up a button in a spreadsheet that adds 2 years to the
value of of the currently selected cell containing a date. the forumla would
look something like this: =DATE(YEAR(A1)+2,MONTH(A2),DAY(A3)) but is there a
way I can have this formula applied to a cell when I press a button? I would
like the cell to contain the result, rather than the formula.
Thanks guys.

On Aug 14, 12:02 pm, Tom Roberts
<TomRobe...@discussions.microsoft.com> wrote:
> I would like to set up a button in a spreadsheet that adds 2 years to the
> value of of the currently selected cell containing a date. the forumla would
> look something like this: =DATE(YEAR(A1)+2,MONTH(A2),DAY(A3)) but is there a
> way I can have this formula applied to a cell when I press a button? I would
> like the cell to contain the result, rather than the formula.
> Thanks guys.

try this event for your button:

Private Sub AddTwoYears_Click()

Dim CurrentValue As Date
On Error GoTo CannotConvertToDate
Let CurrentValue = Selection.Value
On Error GoTo 0
Selection.Value = DateSerial(Year(CurrentValue) + 2,
Month(CurrentValue), Day(CurrentValue))
Exit Sub

CannotConvertToDate:
MsgBox "Selection is not a date"

End Sub

Brian Herbert Withun

Thanks Brian, that was exactly what I was looking for. Makes me realize I
need to learn VB!

"Brian Withun" wrote:

> On Aug 14, 12:02 pm, Tom Roberts
> <TomRobe...@discussions.microsoft.com> wrote:
> > I would like to set up a button in a spreadsheet that adds 2 years to the
> > value of of the currently selected cell containing a date. the forumla would
> > look something like this: =DATE(YEAR(A1)+2,MONTH(A2),DAY(A3)) but is there a
> > way I can have this formula applied to a cell when I press a button? I would
> > like the cell to contain the result, rather than the formula.
> > Thanks guys.
>
> try this event for your button:
>
> Private Sub AddTwoYears_Click()
>
> Dim CurrentValue As Date
> On Error GoTo CannotConvertToDate
> Let CurrentValue = Selection.Value
> On Error GoTo 0
> Selection.Value = DateSerial(Year(CurrentValue) + 2,
> Month(CurrentValue), Day(CurrentValue))
> Exit Sub
>
> CannotConvertToDate:
> MsgBox "Selection is not a date"
>
> End Sub
>
> Brian Herbert Withun
>
>