I have a form where I load a date into say textbox1. I would like to forecast
Textbox2 to three weeks after textbox1. What code can I use in Textbox 2 to
do that? What if my Textbox1 date is Jan 2, 2009 and I want to calculate in
Textbox2 to the next three week cycle date after say Mar 1, from Jan 3, 2009.

(i.e.) TextBox1 Value Jan 2 2009
Textbox2 Value (after Mar 3, 2009) Mar 6, 2009
Textbox3 Value Mar 27, 2009

A text box is a string. So you have to convert the string to a serial date
(microsoft date format) add 63 days (3 * 3 * 7) then convert back to a string
to load into a 2nd text box. Here is some code to help.

MyDate = "Jan 2 2009" 'read from text box 1
SerDate = DateValue(MyDate)
Newdate = (3 * 3 * 7) + SerDate '3 cycles of 3 weeks each
TextDate = Format(Newdate, "MMM DD YYYY")
Textbox2 = TextDate


"Tdungate" wrote:

> I have a form where I load a date into say textbox1. I would like to forecast
> Textbox2 to three weeks after textbox1. What code can I use in Textbox 2 to
> do that? What if my Textbox1 date is Jan 2, 2009 and I want to calculate in
> Textbox2 to the next three week cycle date after say Mar 1, from Jan 3, 2009.
>
> (i.e.) TextBox1 Value Jan 2 2009
> Textbox2 Value (after Mar 3, 2009) Mar 6, 2009
> Textbox3 Value Mar 27, 2009
>

Joel I added the following and got a runtime error 13 - type mismatch on the
Serdate line:

CWW1.Value = Combobox1.Column(23)
Me.CWW1.Value = Format(Me.CWW1.Value, "mmm dd, yyyy")
MyDate = CWW1.Value 'read from text box 1
SerDate = DateValue(MyDate)
Newdate = (3 * 3 * 7) + SerDate '3 cycles of 3 weeks each
TextDate = Format(Newdate, "MMM DD YYYY")
CWW2.Value = TextDate

"Joel" wrote:

> A text box is a string. So you have to convert the string to a serial date
> (microsoft date format) add 63 days (3 * 3 * 7) then convert back to a string
> to load into a 2nd text box. Here is some code to help.
>
> MyDate = "Jan 2 2009" 'read from text box 1
> SerDate = DateValue(MyDate)
> Newdate = (3 * 3 * 7) + SerDate '3 cycles of 3 weeks each
> TextDate = Format(Newdate, "MMM DD YYYY")
> Textbox2 = TextDate
>
>
> "Tdungate" wrote:
>
> > I have a form where I load a date into say textbox1. I would like to forecast
> > Textbox2 to three weeks after textbox1. What code can I use in Textbox 2 to
> > do that? What if my Textbox1 date is Jan 2, 2009 and I want to calculate in
> > Textbox2 to the next three week cycle date after say Mar 1, from Jan 3, 2009.
> >
> > (i.e.) TextBox1 Value Jan 2 2009
> > Textbox2 Value (after Mar 3, 2009) Mar 6, 2009
> > Textbox3 Value Mar 27, 2009
> >

Dungate If I am correct then you can add 21 in first textbox then add 21 with
this textbox for another box. If text box refer to the value is in "Text"
revert back to think me again.
Thx

"Tdungate" wrote:

> I have a form where I load a date into say textbox1. I would like to forecast
> Textbox2 to three weeks after textbox1. What code can I use in Textbox 2 to
> do that? What if my Textbox1 date is Jan 2, 2009 and I want to calculate in
> Textbox2 to the next three week cycle date after say Mar 1, from Jan 3, 2009.
>
> (i.e.) TextBox1 Value Jan 2 2009
> Textbox2 Value (after Mar 3, 2009) Mar 6, 2009
> Textbox3 Value Mar 27, 2009
>

do you have any code for that?

"H" wrote:

> Dungate If I am correct then you can add 21 in first textbox then add 21 with
> this textbox for another box. If text box refer to the value is in "Text"
> revert back to think me again.
> Thx
>
> "Tdungate" wrote:
>
> > I have a form where I load a date into say textbox1. I would like to forecast
> > Textbox2 to three weeks after textbox1. What code can I use in Textbox 2 to
> > do that? What if my Textbox1 date is Jan 2, 2009 and I want to calculate in
> > Textbox2 to the next three week cycle date after say Mar 1, from Jan 3, 2009.
> >
> > (i.e.) TextBox1 Value Jan 2 2009
> > Textbox2 Value (after Mar 3, 2009) Mar 6, 2009
> > Textbox3 Value Mar 27, 2009
> >

A combobox is text. You don't need to format, datavalue will do that
automatically.

CWW1.Value = Combobox1.Column(23)
SerDate = DateValue(CWW1.Value)
Newdate = (3 * 3 * 7) + SerDate '3 cycles of 3 weeks each
TextDate = Format(Newdate, "MMM DD YYYY")
CWW2.Value = TextDate

"Tdungate" wrote:

> Joel I added the following and got a runtime error 13 - type mismatch on the
> Serdate line:
>
> CWW1.Value = Combobox1.Column(23)
> Me.CWW1.Value = Format(Me.CWW1.Value, "mmm dd, yyyy")
> MyDate = CWW1.Value 'read from text box 1
> SerDate = DateValue(MyDate)
> Newdate = (3 * 3 * 7) + SerDate '3 cycles of 3 weeks each
> TextDate = Format(Newdate, "MMM DD YYYY")
> CWW2.Value = TextDate
>
> "Joel" wrote:
>
> > A text box is a string. So you have to convert the string to a serial date
> > (microsoft date format) add 63 days (3 * 3 * 7) then convert back to a string
> > to load into a 2nd text box. Here is some code to help.
> >
> > MyDate = "Jan 2 2009" 'read from text box 1
> > SerDate = DateValue(MyDate)
> > Newdate = (3 * 3 * 7) + SerDate '3 cycles of 3 weeks each
> > TextDate = Format(Newdate, "MMM DD YYYY")
> > Textbox2 = TextDate
> >
> >
> > "Tdungate" wrote:
> >
> > > I have a form where I load a date into say textbox1. I would like to forecast
> > > Textbox2 to three weeks after textbox1. What code can I use in Textbox 2 to
> > > do that? What if my Textbox1 date is Jan 2, 2009 and I want to calculate in
> > > Textbox2 to the next three week cycle date after say Mar 1, from Jan 3, 2009.
> > >
> > > (i.e.) TextBox1 Value Jan 2 2009
> > > Textbox2 Value (after Mar 3, 2009) Mar 6, 2009
> > > Textbox3 Value Mar 27, 2009
> > >

I used your code and I am still getting the same runtime error 13 - type
mismatch on the Serdate line:


"Joel" wrote:

> A combobox is text. You don't need to format, datavalue will do that
> automatically.
>
> CWW1.Value = Combobox1.Column(23)
> SerDate = DateValue(CWW1.Value)
> Newdate = (3 * 3 * 7) + SerDate '3 cycles of 3 weeks each
> TextDate = Format(Newdate, "MMM DD YYYY")
> CWW2.Value = TextDate
>
> "Tdungate" wrote:
>
> > Joel I added the following and got a runtime error 13 - type mismatch on the
> > Serdate line:
> >
> > CWW1.Value = Combobox1.Column(23)
> > Me.CWW1.Value = Format(Me.CWW1.Value, "mmm dd, yyyy")
> > MyDate = CWW1.Value 'read from text box 1
> > SerDate = DateValue(MyDate)
> > Newdate = (3 * 3 * 7) + SerDate '3 cycles of 3 weeks each
> > TextDate = Format(Newdate, "MMM DD YYYY")
> > CWW2.Value = TextDate
> >
> > "Joel" wrote:
> >
> > > A text box is a string. So you have to convert the string to a serial date
> > > (microsoft date format) add 63 days (3 * 3 * 7) then convert back to a string
> > > to load into a 2nd text box. Here is some code to help.
> > >
> > > MyDate = "Jan 2 2009" 'read from text box 1
> > > SerDate = DateValue(MyDate)
> > > Newdate = (3 * 3 * 7) + SerDate '3 cycles of 3 weeks each
> > > TextDate = Format(Newdate, "MMM DD YYYY")
> > > Textbox2 = TextDate
> > >
> > >
> > > "Tdungate" wrote:
> > >
> > > > I have a form where I load a date into say textbox1. I would like to forecast
> > > > Textbox2 to three weeks after textbox1. What code can I use in Textbox 2 to
> > > > do that? What if my Textbox1 date is Jan 2, 2009 and I want to calculate in
> > > > Textbox2 to the next three week cycle date after say Mar 1, from Jan 3, 2009.
> > > >
> > > > (i.e.) TextBox1 Value Jan 2 2009
> > > > Textbox2 Value (after Mar 3, 2009) Mar 6, 2009
> > > > Textbox3 Value Mar 27, 2009
> > > >

This line?
SerDate = DateValue(CWW1.Value)

What did you type into that CWW1 textbox?

I bet excel couldn't tell that you were trying to type a date.

Tdungate wrote:
>
> I used your code and I am still getting the same runtime error 13 - type
> mismatch on the Serdate line:
>
> "Joel" wrote:
>
> > A combobox is text. You don't need to format, datavalue will do that
> > automatically.
> >
> > CWW1.Value = Combobox1.Column(23)
> > SerDate = DateValue(CWW1.Value)
> > Newdate = (3 * 3 * 7) + SerDate '3 cycles of 3 weeks each
> > TextDate = Format(Newdate, "MMM DD YYYY")
> > CWW2.Value = TextDate
> >
> > "Tdungate" wrote:
> >
> > > Joel I added the following and got a runtime error 13 - type mismatch on the
> > > Serdate line:
> > >
> > > CWW1.Value = Combobox1.Column(23)
> > > Me.CWW1.Value = Format(Me.CWW1.Value, "mmm dd, yyyy")
> > > MyDate = CWW1.Value 'read from text box 1
> > > SerDate = DateValue(MyDate)
> > > Newdate = (3 * 3 * 7) + SerDate '3 cycles of 3 weeks each
> > > TextDate = Format(Newdate, "MMM DD YYYY")
> > > CWW2.Value = TextDate
> > >
> > > "Joel" wrote:
> > >
> > > > A text box is a string. So you have to convert the string to a serial date
> > > > (microsoft date format) add 63 days (3 * 3 * 7) then convert back to a string
> > > > to load into a 2nd text box. Here is some code to help.
> > > >
> > > > MyDate = "Jan 2 2009" 'read from text box 1
> > > > SerDate = DateValue(MyDate)
> > > > Newdate = (3 * 3 * 7) + SerDate '3 cycles of 3 weeks each
> > > > TextDate = Format(Newdate, "MMM DD YYYY")
> > > > Textbox2 = TextDate
> > > >
> > > >
> > > > "Tdungate" wrote:
> > > >
> > > > > I have a form where I load a date into say textbox1. I would like to forecast
> > > > > Textbox2 to three weeks after textbox1. What code can I use in Textbox 2 to
> > > > > do that? What if my Textbox1 date is Jan 2, 2009 and I want to calculate in
> > > > > Textbox2 to the next three week cycle date after say Mar 1, from Jan 3, 2009.
> > > > >
> > > > > (i.e.) TextBox1 Value Jan 2 2009
> > > > > Textbox2 Value (after Mar 3, 2009) Mar 6, 2009
> > > > > Textbox3 Value Mar 27, 2009
> > > > >

--

Dave Peterson

CWW1.Value = Combobox1.Column(23) is based on a value from a drop-down box.
the value in the spreadsheet is 01/02/2009.

"Dave Peterson" wrote:

> This line?
> SerDate = DateValue(CWW1.Value)
>
> What did you type into that CWW1 textbox?
>
> I bet excel couldn't tell that you were trying to type a date.
>
> Tdungate wrote:
> >
> > I used your code and I am still getting the same runtime error 13 - type
> > mismatch on the Serdate line:
> >
> > "Joel" wrote:
> >
> > > A combobox is text. You don't need to format, datavalue will do that
> > > automatically.
> > >
> > > CWW1.Value = Combobox1.Column(23)
> > > SerDate = DateValue(CWW1.Value)
> > > Newdate = (3 * 3 * 7) + SerDate '3 cycles of 3 weeks each
> > > TextDate = Format(Newdate, "MMM DD YYYY")
> > > CWW2.Value = TextDate
> > >
> > > "Tdungate" wrote:
> > >
> > > > Joel I added the following and got a runtime error 13 - type mismatch on the
> > > > Serdate line:
> > > >
> > > > CWW1.Value = Combobox1.Column(23)
> > > > Me.CWW1.Value = Format(Me.CWW1.Value, "mmm dd, yyyy")
> > > > MyDate = CWW1.Value 'read from text box 1
> > > > SerDate = DateValue(MyDate)
> > > > Newdate = (3 * 3 * 7) + SerDate '3 cycles of 3 weeks each
> > > > TextDate = Format(Newdate, "MMM DD YYYY")
> > > > CWW2.Value = TextDate
> > > >
> > > > "Joel" wrote:
> > > >
> > > > > A text box is a string. So you have to convert the string to a serial date
> > > > > (microsoft date format) add 63 days (3 * 3 * 7) then convert back to a string
> > > > > to load into a 2nd text box. Here is some code to help.
> > > > >
> > > > > MyDate = "Jan 2 2009" 'read from text box 1
> > > > > SerDate = DateValue(MyDate)
> > > > > Newdate = (3 * 3 * 7) + SerDate '3 cycles of 3 weeks each
> > > > > TextDate = Format(Newdate, "MMM DD YYYY")
> > > > > Textbox2 = TextDate
> > > > >
> > > > >
> > > > > "Tdungate" wrote:
> > > > >
> > > > > > I have a form where I load a date into say textbox1. I would like to forecast
> > > > > > Textbox2 to three weeks after textbox1. What code can I use in Textbox 2 to
> > > > > > do that? What if my Textbox1 date is Jan 2, 2009 and I want to calculate in
> > > > > > Textbox2 to the next three week cycle date after say Mar 1, from Jan 3, 2009.
> > > > > >
> > > > > > (i.e.) TextBox1 Value Jan 2 2009
> > > > > > Textbox2 Value (after Mar 3, 2009) Mar 6, 2009
> > > > > > Textbox3 Value Mar 27, 2009
> > > > > >
>
> --
>
> Dave Peterson
>

What's CWW1?

If it's a range, then the .value may already be a date, so datevalue() won't
work.

This worked ok for me:

Option Explicit
Private Sub CommandButton1_Click()

Dim myDate As Date
With Me.ComboBox1
If .ListIndex < 0 Then
MsgBox "nothing selected"
Else
On Error Resume Next
myDate = CDate(.List(.ListIndex - 1, 1))
If Err.Number <> 0 Then
MsgBox Me.ComboBox1.Value & vbLf & " doesn't look like a date"
Err.Clear
Else
MsgBox myDate
End If
End If
End With
On Error GoTo 0

End Sub
Private Sub UserForm_Initialize()
Dim iCtr As Long

With Me.ComboBox1
.ColumnCount = 2
For iCtr = DateSerial(Year(Date), Month(Date), 1) _
To DateSerial(Year(Date), Month(Date) + 1, 0)
.AddItem "A" & iCtr
.List(.ListCount - 1, 1) = Format(iCtr, "mm/dd/yyyy")
Next iCtr
End With
End Sub

Tdungate wrote:
>
> CWW1.Value = Combobox1.Column(23) is based on a value from a drop-down box.
> the value in the spreadsheet is 01/02/2009.
>
> "Dave Peterson" wrote:
>
> > This line?
> > SerDate = DateValue(CWW1.Value)
> >
> > What did you type into that CWW1 textbox?
> >
> > I bet excel couldn't tell that you were trying to type a date.
> >
> > Tdungate wrote:
> > >
> > > I used your code and I am still getting the same runtime error 13 - type
> > > mismatch on the Serdate line:
> > >
> > > "Joel" wrote:
> > >
> > > > A combobox is text. You don't need to format, datavalue will do that
> > > > automatically.
> > > >
> > > > CWW1.Value = Combobox1.Column(23)
> > > > SerDate = DateValue(CWW1.Value)
> > > > Newdate = (3 * 3 * 7) + SerDate '3 cycles of 3 weeks each
> > > > TextDate = Format(Newdate, "MMM DD YYYY")
> > > > CWW2.Value = TextDate
> > > >
> > > > "Tdungate" wrote:
> > > >
> > > > > Joel I added the following and got a runtime error 13 - type mismatch on the
> > > > > Serdate line:
> > > > >
> > > > > CWW1.Value = Combobox1.Column(23)
> > > > > Me.CWW1.Value = Format(Me.CWW1.Value, "mmm dd, yyyy")
> > > > > MyDate = CWW1.Value 'read from text box 1
> > > > > SerDate = DateValue(MyDate)
> > > > > Newdate = (3 * 3 * 7) + SerDate '3 cycles of 3 weeks each
> > > > > TextDate = Format(Newdate, "MMM DD YYYY")
> > > > > CWW2.Value = TextDate
> > > > >
> > > > > "Joel" wrote:
> > > > >
> > > > > > A text box is a string. So you have to convert the string to a serial date
> > > > > > (microsoft date format) add 63 days (3 * 3 * 7) then convert back to a string
> > > > > > to load into a 2nd text box. Here is some code to help.
> > > > > >
> > > > > > MyDate = "Jan 2 2009" 'read from text box 1
> > > > > > SerDate = DateValue(MyDate)
> > > > > > Newdate = (3 * 3 * 7) + SerDate '3 cycles of 3 weeks each
> > > > > > TextDate = Format(Newdate, "MMM DD YYYY")
> > > > > > Textbox2 = TextDate
> > > > > >
> > > > > >
> > > > > > "Tdungate" wrote:
> > > > > >
> > > > > > > I have a form where I load a date into say textbox1. I would like to forecast
> > > > > > > Textbox2 to three weeks after textbox1. What code can I use in Textbox 2 to
> > > > > > > do that? What if my Textbox1 date is Jan 2, 2009 and I want to calculate in
> > > > > > > Textbox2 to the next three week cycle date after say Mar 1, from Jan 3, 2009.
> > > > > > >
> > > > > > > (i.e.) TextBox1 Value Jan 2 2009
> > > > > > > Textbox2 Value (after Mar 3, 2009) Mar 6, 2009
> > > > > > > Textbox3 Value Mar 27, 2009
> > > > > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson