Selected Answer
Carlo.
In the revised file attached, I modifed your sub for changes to D1 as follows (changes in bold):
Private Sub D1_Change()
Me.TotalDevices = Val(Me.D1) + Val(Me.D2) + Val(Me.D3) + Val(Me.D4) + Val(Me.D5) + Val(Me.D6) + _
Val(Me.D7) + Val(Me.D8) + Val(Me.D9) + Val(Me.D10) + Val(Me.D11) + Val(Me.D12)
Call AvDevices
End Sub
which calls this (commented for your guidance):
Private Sub AvDevices()
Dim Ctrl As Control, Vl As Variant, Nm As String
Dim AvNo As Integer, AvTot As Double
' reset variables for average
AvNo = 0
AvTot = 0
' loop through controls
For Each Ctrl In Me.Controls
'check if name contains Perc
Pos = InStr(Ctrl.Name, "Perc")
If Pos > 0 Then
'get value of matching control (without Perc)
Vl = UserForm1.Controls(Left(Ctrl.Name, Pos - 1)).Value
If Vl <> "" Then
'write to this control as a percentage
Ctrl.Text = Format(Vl / Me.TotalDevices.Value, "#.0%")
'increase count and total for avarage
AvNo = AvNo + 1
AvTot = AvTot + Vl
End If
End If
Next Ctrl
' calculate and display average of non-blank values
Me.AverageDevices = Format(AvTot / AvNo, "#.00")
End Sub
That gets the device total and percentages c.f that.
For the time total and average, T1 to T12 update subs have the additions in bold (which avoid odd values when text is deleted and now allows values >24 hrs) whilst leaving your input feature where 2 digits are converted to that many hours.
Private Sub T1_AfterUpdate()
Dim a As String
a = Len(Me.T1)
' don't convert if value delted but update total/ average
If a = 0 Then
Call TotalTime_AfterUpdate
Exit Sub
End If
If a <= 2 Then
On Error Resume Next
Me.T1 = Left(Me.T1, a) & ":" & "00"
'allow any number of hours
Else
Me.T1 = Left(T1, a - 2) & ":" & Right(Me.T1, 2)
End If
Call TotalTime_AfterUpdate
End Sub
which then calls this macro which converts the values in T1 etc. to a numer of minutes (so they can be added correctly as times):
Private Sub TotalTime_AfterUpdate()
Dim TTime As Double, n As Integer, TCount As Integer
Dim HrPart As Double, Minpart As Double
' loop through Time entries
For n = 1 To 12
'If value exists...
If (Me.Controls("T" & n).Value) > "" Then
'... up variables, converting values to minutes
With Me.Controls("T" & n)
HrPart = CDbl(Left(.Value, InStr(.Value, ":") - 1))
Minpart = CDbl(Right(.Value, Len(.Value) - InStr(.Value, ":")))
TTime = TTime + 60 * HrPart + Minpart
TCount = TCount + 1
End With
End If
Next n
' convert minutes to hh mm
HrPart = Application.WorksheetFunction.RoundDown(TTime / 60, 0)
Minpart = TTime - HrPart * 60
Me.TotalTime.Text = HrPart & ":" & Format(Minpart, "0#")
' pass values to other sub for averages
Call AvTime(TTime, TCount)
End Sub
which in turn sends the collected values TTime and TCount to this macro:
Private Sub AvTime(TTime As Double, TCount As Integer)
Dim Av As Double
'Avoid Div0 error if no values entered or all values deleted
If TCount = 0 Then TCount = 1
' calculate average minutes
Av = TTime / TCount
' convert to hh mm
HrPart = Application.WorksheetFunction.RoundDown(Av / 60, 0)
Minpart = Round((Av - HrPart * 60), 0)
Me.AverageTime.Text = HrPart & ":" & Format(Minpart, "0#")
End Sub
which gives the average time in your userform.
Hope this works for you..
