How can i ADD 1 year to Textbox32's date entered and have it placed into
Textbox33 ?

Private Sub TextBox32_AfterUpdate()
TextBox32.Text = Format(TextBox32.Text, "ddd dd mmmm yyyy")
If TextBox32.Value <> "" Then TextBox33.Value = TextBox32.Value ' <=== Gives
me an error
End Sub

Corey....

If you already know the exact format the user will use to enter the date -
"2009-02-23" or "Feb 23, 2009" or whatever - then you can take the text value
of Textbox32, parse out the year, add 1 to it, insert it back into the date
and put it in Textbox33.

But personally I prefer to let people use their own date formats without
making my program impose a standard. To do that, use
DATEVALUE(Textbox32.Text) to produce a date value. Depending on what "adding
one year" means to you, you can simply add 365 to that value to get a date
that is 365 days later, or you can use the DateAdd function to add a year to
it, or whatever. Then place - or you can Sounds to me like you need to
convert Textbox32's text value to a date, using the DATEVALUE function. You
can then use the Format function to turn the date back into a string and put
that in Textbox33.

--- "Corey ...." wrote:
> How can i ADD 1 year to Textbox32's date entered and have it placed into
> Textbox33 ?

Thanks for the helpful info Bob.

Was still getting an error there for a bit, but had the textbox formatted
BEFORE i used the DateValue().

All is good now thatks.

Corey....

"Bob Bridges" <(E-Mail Removed)> wrote in message
news:BC80305E-3530-4075-8C8F-(E-Mail Removed)...
> If you already know the exact format the user will use to enter the date -
> "2009-02-23" or "Feb 23, 2009" or whatever - then you can take the text
> value
> of Textbox32, parse out the year, add 1 to it, insert it back into the
> date
> and put it in Textbox33.
>
> But personally I prefer to let people use their own date formats without
> making my program impose a standard. To do that, use
> DATEVALUE(Textbox32.Text) to produce a date value. Depending on what
> "adding
> one year" means to you, you can simply add 365 to that value to get a date
> that is 365 days later, or you can use the DateAdd function to add a year
> to
> it, or whatever. Then place - or you can Sounds to me like you need to
> convert Textbox32's text value to a date, using the DATEVALUE function.
> You
> can then use the Format function to turn the date back into a string and
> put
> that in Textbox33.
>
> --- "Corey ...." wrote:
>> How can i ADD 1 year to Textbox32's date entered and have it placed into
>> Textbox33 ?

Here is one way (although I'm not sure what event procedure you will want to
put it in)...

Dim D As Date
If IsDate(TextBox32.Text) Then
D = CDate(TextBox32.Text)
TextBox33.Text = D + 365 - (Day(D) <> Day(D + 365))
End If

--
Rick (MVP - Excel)


"Corey ...." <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> How can i ADD 1 year to Textbox32's date entered and have it placed into
> Textbox33 ?
>
> Private Sub TextBox32_AfterUpdate()
> TextBox32.Text = Format(TextBox32.Text, "ddd dd mmmm yyyy")
> If TextBox32.Value <> "" Then TextBox33.Value = TextBox32.Value ' <===
> Gives me an error
> End Sub
>
> Corey....
>

Be careful if you extract the year and add 1 to it. You wouldn't want Feb
29, 2008 to become Feb 29, 2009. Add the 365 days to the Date instead of
changing the year.
Mike F
"Rick Rothstein" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Here is one way (although I'm not sure what event procedure you will want
> to put it in)...
>
> Dim D As Date
> If IsDate(TextBox32.Text) Then
> D = CDate(TextBox32.Text)
> TextBox33.Text = D + 365 - (Day(D) <> Day(D + 365))
> End If
>
> --
> Rick (MVP - Excel)
>
>
> "Corey ...." <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> How can i ADD 1 year to Textbox32's date entered and have it placed into
>> Textbox33 ?
>>
>> Private Sub TextBox32_AfterUpdate()
>> TextBox32.Text = Format(TextBox32.Text, "ddd dd mmmm yyyy")
>> If TextBox32.Value <> "" Then TextBox33.Value = TextBox32.Value ' <===
>> Gives me an error
>> End Sub
>>
>> Corey....
>>
>

On Tue, 24 Feb 2009 06:48:56 -0500, "Mike Fogleman" <(E-Mail Removed)>
wrote:

>Be careful if you extract the year and add 1 to it. You wouldn't want Feb
>29, 2008 to become Feb 29, 2009. Add the 365 days to the Date instead of
>changing the year.

How, exactly, did you get a result of Feb 29, 2009?

Although you may get unexpected answers adding 1 year to the 29-feb-2008,
(i.e., depending on your method, either 28-feb-2009 or 1-mar-2009), I don't see
how you obtained a result of 29-feb-2009.

To the best of my knowledge, neither the Date data type, nor a worksheet cell
formatted as Date, will support a date of 29-feb-2009.

I suppose you could construct a text string, but that would not be recognized
or used as a date by either VBA or Excel.
--ron

Did you mean your message to be a response to my posting? If so, that is not
what my code does. If you are not sure, try it out... it will not produce
Feb 29th in any year.

--
Rick (MVP - Excel)


"Mike Fogleman" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Be careful if you extract the year and add 1 to it. You wouldn't want Feb
> 29, 2008 to become Feb 29, 2009. Add the 365 days to the Date instead of
> changing the year.
> Mike F
> "Rick Rothstein" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Here is one way (although I'm not sure what event procedure you will want
>> to put it in)...
>>
>> Dim D As Date
>> If IsDate(TextBox32.Text) Then
>> D = CDate(TextBox32.Text)
>> TextBox33.Text = D + 365 - (Day(D) <> Day(D + 365))
>> End If
>>
>> --
>> Rick (MVP - Excel)
>>
>>
>> "Corey ...." <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> How can i ADD 1 year to Textbox32's date entered and have it placed into
>>> Textbox33 ?
>>>
>>> Private Sub TextBox32_AfterUpdate()
>>> TextBox32.Text = Format(TextBox32.Text, "ddd dd mmmm yyyy")
>>> If TextBox32.Value <> "" Then TextBox33.Value = TextBox32.Value ' <===
>>> Gives me an error
>>> End Sub
>>>
>>> Corey....
>>>
>>
>
>

No Rick, I endorse your code, just warning about creating a False date with
a string, like Ron mentioned could happen but would not really be a
"recognized" date by Excel.
"Rick Rothstein" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Did you mean your message to be a response to my posting? If so, that is
> not what my code does. If you are not sure, try it out... it will not
> produce Feb 29th in any year.
>
> --
> Rick (MVP - Excel)
>
>
> "Mike Fogleman" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Be careful if you extract the year and add 1 to it. You wouldn't want Feb
>> 29, 2008 to become Feb 29, 2009. Add the 365 days to the Date instead of
>> changing the year.
>> Mike F
>> "Rick Rothstein" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> Here is one way (although I'm not sure what event procedure you will
>>> want to put it in)...
>>>
>>> Dim D As Date
>>> If IsDate(TextBox32.Text) Then
>>> D = CDate(TextBox32.Text)
>>> TextBox33.Text = D + 365 - (Day(D) <> Day(D + 365))
>>> End If
>>>
>>> --
>>> Rick (MVP - Excel)
>>>
>>>
>>> "Corey ...." <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> How can i ADD 1 year to Textbox32's date entered and have it placed
>>>> into Textbox33 ?
>>>>
>>>> Private Sub TextBox32_AfterUpdate()
>>>> TextBox32.Text = Format(TextBox32.Text, "ddd dd mmmm yyyy")
>>>> If TextBox32.Value <> "" Then TextBox33.Value = TextBox32.Value ' <===
>>>> Gives me an error
>>>> End Sub
>>>>
>>>> Corey....
>>>>
>>>
>>
>>
>