So i have all the variables calculated, and I slammed
them in a messagebox to make sure they are working and formatted
correctly, but I'm not sure how to get them in the text box of my
userform (because that is where I really want the text). Also, is it
possible to format certain lines in the text box but not others?
specifically, i'd like vacation, sick, and paidleave lines to be red
or bold...
Private Sub UserForm_Initialize()
Dim totalhours As Integer, totalvacation As Integer, totalsick As
Integer
Dim totalotherpaidleave As Integer, remainder As Integer
totalvacation = 0
totalsick = 0
totalotherpaidleave = 0
remainder = 0
Dim r As Integer
totalhours = Range("V29").Value
For r = 13 To 27 ' the rows in the worksheet to find values in
If Range("Timesheet!J" & r).Value = "Bereavement" Then
totalotherpaidleave = totalotherpaidleave +
Range("Timesheet!V" & r).Value
End If 'Ends If loop, which makes it so only =Bereavement
values are acted on.
If Range("Timesheet!J" & r).Value = "Holiday" Then
totalotherpaidleave = totalotherpaidleave +
Range("Timesheet!V" & r).Value
End If 'Ends If loop, which makes it so only =Holiday values
are acted on.
If Range("Timesheet!J" & r).Value = "Jury Duty" Then
totalotherpaidleave = totalotherpaidleave +
Range("Timesheet!V" & r).Value
End If 'Ends If loop, which makes it so only =Jury Duty
values are acted on.
If Range("Timesheet!J" & r).Value = "Other Paid
Time Off" Then
totalotherpaidleave = totalotherpaidleave +
Range("Timesheet!V" & r).Value
End If 'Ends If loop, which makes it so only =Other Paid
Time Off values are acted on.
If Range("Timesheet!J" & r).Value =
"Paternity/Maternity Leave" Then
totalotherpaidleave = totalotherpaidleave +
Range("Timesheet!V" & r).Value
End If 'Ends If loop, which makes it so only
=Paternity/Maternity Leave values are acted on.
If Range("Timesheet!J" & r).Value = "Sick Leave" Then
totalsick = totalsick + Range("Timesheet!V" & r).Value
End If 'Ends If loop, which makes it so only =Sick Leave
values are acted on.
If Range("Timesheet!J" & r).Value = "Vacation" Then
totalvacation = totalvacation + Range("Timesheet!V" &
r).Value
End If 'Ends If loop, which makes it so only =Holiday values
are acted on.
Next 'Iterates r from 3 to 17, and indicates end of For loop
remainder = totalhours - (totalvacation + totalsick +
totalotherpaidleave)
MsgBox "The following is a summary of the hours exported" & vbCr _
& vbTab & "Total hours: " & vbTab & vbTab & totalhours & vbCr _
& vbTab & "Total vacation: " & vbTab & vbTab & totalvacation
& vbCr _
& vbTab & "Total sick leave: " & vbTab & vbTab & totalsick &
vbCr _
& vbTab & "Total other paid leave: " & vbTab &
totalotherpaidleave & vbCr _
& vbTab & "----------------------------" & vbTab & "---" &
vbCr _
& vbTab & "Total normal hours: " & vbTab & remainder
End Sub