| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
textbox autosum
Hi, I have created a userform with 3 textboxes, box1 = Date, box2 = Number of
days and Box3 = box 1 = box 2. I have a code that almost works, see below. heres what I want it to do. Box 1 is a date and box 2 is a number. I want to add the these 2 values together and display the answer in Box 3. The code works however I have to select Box 3 and then press any key for it to calculate the sum. Is their a way to autosum this and display the answer in the textbox. Private Sub TextBox3_Change() If TextBox1.Value = "" Then Exit Sub If TextBox2.Value = "" Then Exit Sub TextBox3.Value = CDate(TextBox1.Value) + CDbl(TextBox2.Value) End Sub Thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
textbox autosum
Hi Woodi,
Try this. It updates after you press enter or tab from TextBox1 or TextBox2 Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) Call UpdateWhenChanged End Sub Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean) Call UpdateWhenChanged End Sub Sub UpdateWhenChanged() If TextBox1.Value = "" Then Exit Sub If TextBox2.Value = "" Then Exit Sub TextBox3.Value = CDate(TextBox1.Value) + CDbl(TextBox2.Value) End Sub -- Regards, OssieMac "Woodi2" wrote: Hi, I have created a userform with 3 textboxes, box1 = Date, box2 = Number of days and Box3 = box 1 = box 2. I have a code that almost works, see below. heres what I want it to do. Box 1 is a date and box 2 is a number. I want to add the these 2 values together and display the answer in Box 3. The code works however I have to select Box 3 and then press any key for it to calculate the sum. Is their a way to autosum this and display the answer in the textbox. Private Sub TextBox3_Change() If TextBox1.Value = "" Then Exit Sub If TextBox2.Value = "" Then Exit Sub TextBox3.Value = CDate(TextBox1.Value) + CDbl(TextBox2.Value) End Sub Thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
textbox autosum
Brilliant, thanks for that and the quick response.
The only other problemt I have with it is it returns the date in the following format in the textbox i.e. 01/29/2009. When I click OK it then changes it to 29/01/2009. How do I change the textbox so that it displays as 29/01/2009 so the user can check the date is correct. Thanks Ian "OssieMac" wrote: Hi Woodi, Try this. It updates after you press enter or tab from TextBox1 or TextBox2 Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) Call UpdateWhenChanged End Sub Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean) Call UpdateWhenChanged End Sub Sub UpdateWhenChanged() If TextBox1.Value = "" Then Exit Sub If TextBox2.Value = "" Then Exit Sub TextBox3.Value = CDate(TextBox1.Value) + CDbl(TextBox2.Value) End Sub -- Regards, OssieMac "Woodi2" wrote: Hi, I have created a userform with 3 textboxes, box1 = Date, box2 = Number of days and Box3 = box 1 = box 2. I have a code that almost works, see below. heres what I want it to do. Box 1 is a date and box 2 is a number. I want to add the these 2 values together and display the answer in Box 3. The code works however I have to select Box 3 and then press any key for it to calculate the sum. Is their a way to autosum this and display the answer in the textbox. Private Sub TextBox3_Change() If TextBox1.Value = "" Then Exit Sub If TextBox2.Value = "" Then Exit Sub TextBox3.Value = CDate(TextBox1.Value) + CDbl(TextBox2.Value) End Sub Thanks |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
textbox autosum
Hi again Woodi,
Not sure what you mean by "When I click OK " . What OK? Anyway the following formats as say 30 Jan 2009. Used alpha method so you can see that it is correct. You can use any of the date formats between the double quotes. TextBox3.Value = Format(CDate(TextBox1.Value) + _ CDbl(TextBox2.Value), "dd mmm yyyy") Note: Space and underscore at the end of a line is a line break in an otherwise single line of code. -- Regards, OssieMac "Woodi2" wrote: Brilliant, thanks for that and the quick response. The only other problemt I have with it is it returns the date in the following format in the textbox i.e. 01/29/2009. When I click OK it then changes it to 29/01/2009. How do I change the textbox so that it displays as 29/01/2009 so the user can check the date is correct. Thanks Ian "OssieMac" wrote: Hi Woodi, Try this. It updates after you press enter or tab from TextBox1 or TextBox2 Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) Call UpdateWhenChanged End Sub Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean) Call UpdateWhenChanged End Sub Sub UpdateWhenChanged() If TextBox1.Value = "" Then Exit Sub If TextBox2.Value = "" Then Exit Sub TextBox3.Value = CDate(TextBox1.Value) + CDbl(TextBox2.Value) End Sub -- Regards, OssieMac "Woodi2" wrote: Hi, I have created a userform with 3 textboxes, box1 = Date, box2 = Number of days and Box3 = box 1 = box 2. I have a code that almost works, see below. heres what I want it to do. Box 1 is a date and box 2 is a number. I want to add the these 2 values together and display the answer in Box 3. The code works however I have to select Box 3 and then press any key for it to calculate the sum. Is their a way to autosum this and display the answer in the textbox. Private Sub TextBox3_Change() If TextBox1.Value = "" Then Exit Sub If TextBox2.Value = "" Then Exit Sub TextBox3.Value = CDate(TextBox1.Value) + CDbl(TextBox2.Value) End Sub Thanks |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
textbox autosum
Thanks OssieMac, that works great. When I mentioned "OK" I meant the OK
button on my userform, apologies for the confusion. Could you answer another? If I send the data from the Userform to i.e. celss e3, e4 and e5, how can I read the data on the userform if i select any of those cells. I not onnly want to input data from the userform but use the userform to edit data. "OssieMac" wrote: Hi again Woodi, Not sure what you mean by "When I click OK " . What OK? Anyway the following formats as say 30 Jan 2009. Used alpha method so you can see that it is correct. You can use any of the date formats between the double quotes. TextBox3.Value = Format(CDate(TextBox1.Value) + _ CDbl(TextBox2.Value), "dd mmm yyyy") Note: Space and underscore at the end of a line is a line break in an otherwise single line of code. -- Regards, OssieMac "Woodi2" wrote: Brilliant, thanks for that and the quick response. The only other problemt I have with it is it returns the date in the following format in the textbox i.e. 01/29/2009. When I click OK it then changes it to 29/01/2009. How do I change the textbox so that it displays as 29/01/2009 so the user can check the date is correct. Thanks Ian "OssieMac" wrote: Hi Woodi, Try this. It updates after you press enter or tab from TextBox1 or TextBox2 Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) Call UpdateWhenChanged End Sub Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean) Call UpdateWhenChanged End Sub Sub UpdateWhenChanged() If TextBox1.Value = "" Then Exit Sub If TextBox2.Value = "" Then Exit Sub TextBox3.Value = CDate(TextBox1.Value) + CDbl(TextBox2.Value) End Sub -- Regards, OssieMac "Woodi2" wrote: Hi, I have created a userform with 3 textboxes, box1 = Date, box2 = Number of days and Box3 = box 1 = box 2. I have a code that almost works, see below. heres what I want it to do. Box 1 is a date and box 2 is a number. I want to add the these 2 values together and display the answer in Box 3. The code works however I have to select Box 3 and then press any key for it to calculate the sum. Is their a way to autosum this and display the answer in the textbox. Private Sub TextBox3_Change() If TextBox1.Value = "" Then Exit Sub If TextBox2.Value = "" Then Exit Sub TextBox3.Value = CDate(TextBox1.Value) + CDbl(TextBox2.Value) End Sub Thanks |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
textbox autosum
Hi Again Woodi,
Do you mean that you want the form displayed while you work on a worksheet? If so, then in the form's properties set ShowModal to false. Ensure that you right click somewhere on the blank form to open the form's properties . (Not on a textbox). However, when you open the form with code it is a good idea to activate the required worksheet otherwise it can open anywhere. Your comment "I send the data from the Userform" How are you doing this? Are you setting the controlsource for the text boxes or doing it with code? If doing it by setting the controlsource, then for your textbox3, the linked cell must be formatted as text otherwise the correct date will appear in the cell and the date in the forms textbox reverts to m/d/y format. Note: that the code I gave you places the date in the textbox as text. If you want to manipulate data with it then you will need the DateValue function. Example; MyDate = Datevalue(Textbox3) Same if using the date in a linked (controlsource) cell when the cell is formatted to text. MyDate = Datevalue(Range("E5")) -- Regards, OssieMac "Woodi2" wrote: Thanks OssieMac, that works great. When I mentioned "OK" I meant the OK button on my userform, apologies for the confusion. Could you answer another? If I send the data from the Userform to i.e. celss e3, e4 and e5, how can I read the data on the userform if i select any of those cells. I not onnly want to input data from the userform but use the userform to edit data. "OssieMac" wrote: Hi again Woodi, Not sure what you mean by "When I click OK " . What OK? Anyway the following formats as say 30 Jan 2009. Used alpha method so you can see that it is correct. You can use any of the date formats between the double quotes. TextBox3.Value = Format(CDate(TextBox1.Value) + _ CDbl(TextBox2.Value), "dd mmm yyyy") Note: Space and underscore at the end of a line is a line break in an otherwise single line of code. -- Regards, OssieMac "Woodi2" wrote: Brilliant, thanks for that and the quick response. The only other problemt I have with it is it returns the date in the following format in the textbox i.e. 01/29/2009. When I click OK it then changes it to 29/01/2009. How do I change the textbox so that it displays as 29/01/2009 so the user can check the date is correct. Thanks Ian "OssieMac" wrote: Hi Woodi, Try this. It updates after you press enter or tab from TextBox1 or TextBox2 Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) Call UpdateWhenChanged End Sub Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean) Call UpdateWhenChanged End Sub Sub UpdateWhenChanged() If TextBox1.Value = "" Then Exit Sub If TextBox2.Value = "" Then Exit Sub TextBox3.Value = CDate(TextBox1.Value) + CDbl(TextBox2.Value) End Sub -- Regards, OssieMac "Woodi2" wrote: Hi, I have created a userform with 3 textboxes, box1 = Date, box2 = Number of days and Box3 = box 1 = box 2. I have a code that almost works, see below. heres what I want it to do. Box 1 is a date and box 2 is a number. I want to add the these 2 values together and display the answer in Box 3. The code works however I have to select Box 3 and then press any key for it to calculate the sum. Is their a way to autosum this and display the answer in the textbox. Private Sub TextBox3_Change() If TextBox1.Value = "" Then Exit Sub If TextBox2.Value = "" Then Exit Sub TextBox3.Value = CDate(TextBox1.Value) + CDbl(TextBox2.Value) End Sub Thanks |
|
#7
|
|||
|
|||
|
You can do this as follows
Copy following code and paste into your macro’s code window Private Sub addDate() If TextBox1.Value = "" Then Exit Sub If TextBox2.Value = "" Then Exit Sub TextBox3.Value = CDate(TextBox1.Value) + CDbl(TextBox2.Value) End Sub Private Sub TextBox1_Change() Call addDate End Sub Private Sub TextBox2_Change() Call addDate End Sub Have a nice time.... Chris ------ Convert your Excel spreadsheet into an online calculator. http://www.spreadsheetconverter.com |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| AutoSum | New Users to Excel | |||
| autosum | Excel Discussion (Misc queries) | |||
| how do i set up autosum as a % | Excel Worksheet Functions | |||
| AutoSum | Excel Worksheet Functions | |||
| AutoSum | Excel Worksheet Functions | |||
Linear Mode
