Help with VBA UserForms simple math adding two textboxes (WIN7, Excel7)

[ozzborn]

I am creating a userform that is pretty simple.

The form posts the current date, Name of an employee, start date and an end date for leave.

So it has
1 txtbox for the current date
2 ComboBox for employee drop down list of names
3 txtbox for start date
4. txtbox for end date

I would like without have to create another command button have the two date txtboxes subtract from each other and return the # of days taken to post in a flat database with the other information.

Here is my current code and it is work really good as it is now but it just posts current date, name, and the two days. I would like it to post the # of days too.

I found this code searching the web " TextBox3.Value = CDbl(TextBox1.Value) - CDbl(TextBox2.Value)". I have no idea where it shouuld go? I am super new to VBA,,,this is my first code.

Private Sub cmdAddData_Click()
Dim ws As Worksheet
Dim Addto As Range

Set ws = Sheet2
Set Addto = ws.Range("c65356").End(xlUp).Offset(1, 0)

With ws
Addto = txtDate.Value
Addto.Offset(0, 1).Value = cboName.Value
Addto.Offset(0, 2).Value = txtStart.Value
Addto.Offset(0, 3).Value = txtEnd.Value
End With

txtDate.Value = Format(Date, "Medium Date")
cboName.Value = ""
txtStart.Value = ""
txtEnd.Value = ""
End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub cmdView_Click()
Unload Me
Sheet2.Select
Sheet2.Range("A1").Select
End Sub

Private Sub UserForm_Click()

End Sub

Private Sub UserForm_Initialize()
txtDate.Value = Format(Date, "Medium Date")
Me.cboName.SetFocus

End Sub

 

[mjbeam]

Private Sub cmdAddData_Click()
 Dim ws As Worksheet
 Dim Addto As Range

 Set ws = Sheet2
 Set Addto = ws.Range("c65356").End(xlUp).Offset(1, 0)

 On Error Resume Next
 txtDays.Value = CDbl(txtEnd.Value) - CDbl(txtStart.Value)
 On Error Goto 0

 With ws
 Addto = txtDate.Value
 Addto.Offset(0, 1).Value = cboName.Value
 Addto.Offset(0, 2).Value = txtStart.Value
 Addto.Offset(0, 3).Value = txtEnd.Value
 Addto.Offset(0, 4).Value = txtDays.Value 'This assumes your "Days" textbox is named txtDays
 End With

 txtDate.Value = Format(Date, "Medium Date")
 cboName.Value = ""
 txtStart.Value = ""
 txtEnd.Value = ""
 txtDays.Value = ""
End Sub

 

[ozzborn]

mjbeam, thank you I will try the code

 

[Norie]

Try DateDiff.

TextBox3.Value = DateDiff("d",txtStart.Value , txtEnd.Value)

[/code]

 

[ozzborn]

Private Sub cmdAddData_Click()
Dim ws As Worksheet
Dim Addto As Range

Set ws = Sheet2
Set Addto = ws.Range("c65356").End(xlUp).Offset(1, 0)

On Error Resume Next
txtDays.Value = CDbl(txtEnd.Value) - CDbl(txtStart.Value)
On Error GoTo 0

With ws
Addto = txtDate.Value
Addto.Offset(0, 1).Value = cboName.Value
Addto.Offset(0, 2).Value = txtStart.Value
Addto.Offset(0, 3).Value = txtEnd.Value
Addto.Offset(0, 4).Value = txtDays.Value
End With

txtDate.Value = Format(Date, "Medium Date")
cboName.Value = ""
txtStart.Value = ""
txtEnd.Value = ""
txtDays.Value = ""
End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub cmdView_Click()
Unload Me
Sheet2.Select
Sheet2.Range("A1").Select
End Sub

Private Sub UserForm_Click()

End Sub

Private Sub UserForm_Initialize()
txtDate.Value = Format(Date, "Medium Date")
Me.cboName.SetFocus

End Sub

The code recommended by mjbeam did not post the calculated value in the difference in the days. I could place any number in the txtDays text box and it would post to the spreadsheet.

Private Sub cmdAddData_Click()
Dim ws As Worksheet
Dim Addto As Range

Set ws = Sheet2
Set Addto = ws.Range("c65356").End(xlUp).Offset(1, 0)

On Error Resume Next
txtDays.Value = DateDiff("d", txtStart.Value, txtEnd.Value)
On Error GoTo 0

With ws
Addto = txtDate.Value
Addto.Offset(0, 1).Value = cboName.Value
Addto.Offset(0, 2).Value = txtStart.Value
Addto.Offset(0, 3).Value = txtEnd.Value
Addto.Offset(0, 4).Value = txtDays.Value
End With

txtDate.Value = Format(Date, "Medium Date")
cboName.Value = ""
txtStart.Value = ""
txtEnd.Value = ""
txtDays.Value = ""
End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub cmdView_Click()
Unload Me
Sheet2.Select
Sheet2.Range("A1").Select
End Sub

Private Sub UserForm_Click()

End Sub

Private Sub UserForm_Initialize()
txtDate.Value = Format(Date, "Medium Date")
Me.cboName.SetFocus

End Sub

The code recommended by norie did work to some degree. It posted the difference of the dates to the worksheet. However when you tabed to the txtDays text box in the userform no value was shown in that txt box. This would be confusing to the person filling in the from. It also allowed you to type any value and over rid the math formula and would place any value as days. If the txtbox would populate the value once you place the last date I think that would be fine.

Thanks to both of you for your assistance.

Scott

 

[Norie]

Scott

Did you change the code I posted so the result went into txtDays?

Also, did you put the code in the Exit event of the txtDate textbox?

 

[ozzborn]

Norie, your going to have to bear with me I am super novice. "Did you change the code I posted so the result went into txtDays?" not sure what that means so I am sure I did not do what your asking.

"Also, did you put the code in the Exit event of the txtDate textbox?" again not sure what that means so I am sure I did not do that either.

I posted the code for your recommendation ...all of it

 

[ozzborn]

Did you change the code I posted so the result went into txtDays?

Also, did you put the code in the Exit event of the txtDate textbox?

txtDays.Value = CDbl(txtEnd.Value) - CDbl(txtStart.Value)

I need help with this line of code. Norie asked me some questions that I have been trying to research
but have not been able to figure out.

I assume that from his comment I need to edit the code to tell it to post the result into txtDays and
then place code in the Textbox txtDays. To make sure the results are posted within the box?

The entire code is in the prior post for context.

Thank you for any help.

 

[ozzborn]

Help would be appreciate?

 

[Norie]

This is what I meant.

Private Sub txtEnd_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    txtDays.Value = DateDiff("d",txtStart.Value , txtEnd.Value)

End Sub

This will put the no of days between the start and end dates in the textbox txtDays as soon as you exit the textbox txtEnd.

Note, you might want to add some checks to see if an actual datse have been entered and/or the textboxes aren't empty.