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

ozzborn

Board Regular
Joined
Sep 14, 2011
Messages
84
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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.

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
 
Last edited by a moderator:

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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)

 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
 
Upvote 0
Try DateDiff.
Code:
TextBox3.Value = DateDiff("d",txtStart.Value , txtEnd.Value)
[/code]
 
Upvote 0
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)

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.

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)

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
 
Upvote 0
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?
 
Upvote 0
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
 
Last edited:
Upvote 0
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?

Code:
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.
 
Upvote 0
This is what I meant.
Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top