| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate Textbox value based on another textbox value
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculate Textbox value based on another textbox value
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculate Textbox value based on another textbox value
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculate Textbox value based on another textbox value
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculate Textbox value based on another textbox value
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculate Textbox value based on another textbox value
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculate Textbox value based on another textbox value
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculate Textbox value based on another textbox value
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculate Textbox value based on another textbox value
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculate Textbox value based on another textbox value
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculate Textbox value based on another textbox value.doc | Excel Discussion (Misc queries) | |||
| Calculate Textbox value based on another textbox value | Excel Discussion (Misc queries) | |||
| HELP! I Lost The Ability To Advance From TextBox To TextBox With the ENTER Or The TAB Keys | Excel Programming | |||
| Textbox Bug? Missing/delayed update of textbox filled via VBA | Excel Programming | |||
| Textbox Bug? Missing/delayed update of textbox filled via VBA | Excel Programming | |||
Linear Mode
