textbox autosum

[Woodi2]

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

 

[OssieMac]

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

 

[Woodi2]

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]

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.

 

[Woodi2]

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]

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"))