I'm new to userforms and a self-taught VBA code writer. I have several userforms generated and named the textboxes all unique names. My user would open "Sort_Entry_Form" put in some data then click on "Enter Downtime Entries" button. This brings up a new form for them to enter downtime entries in. I have VBA that calculates the page using the "Calculate" button but you can see a lot of code due to all the names being unique. On the "Accept_Click" code, I was trying to use an integer with a For statement to reduce the code writing. This doesn't work. I'm guessing I didn't name the textboxes well, or there is a better way to identify them in VBA than each individual name. I don't see how to attach my workbook.
[/CODE][/CODE]
VBA Code:
[CODE=rich][CODE=vba]Private Sub Accept_Click()
Dim i As Integer
Dim LastRow As Long
For i = 1 To 10
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
If (Sort_DwnTm_Equip & i <> "") Then
ActiveWorkbook.Worksheets("Downtime Log").Range("A" & LastRow + 1) = "Sort"
ActiveWorkbook.Worksheets("Downtime Log").Range("B" & LastRow + 1) = Sort_DwnTm_Equip & i
ActiveWorkbook.Worksheets("Downtime Log").Range("C" & LastRow + 1) = Sort_DwnTm_Qty & i
ActiveWorkbook.Worksheets("Downtime Log").Range("D" & LastRow + 1) = CDbl(Sort_DwnTm_Sum & i)
Else
End If
Next
End Sub
Private Sub Sort_Calculate_Downtime_Button_Click()
Dim S_DwnTm_Sum1, S_DwnTm_Sum2, S_DwnTm_Sum3, S_DwnTm_Sum4, S_DwnTm_Sum5 As Double
Dim S_DwnTm_Sum6, S_DwnTm_Sum7, S_DwnTm_Sum8, S_DwnTm_Sum9, S_DwnTm_Sum10 As Double
Dim S_DwnTm_Hr1, S_DwnTm_Hr2, S_DwnTm_Hr3, S_DwnTm_Hr4, S_DwnTm_Hr5 As Double
Dim S_DwnTm_Hr6, S_DwnTm_Hr7, S_DwnTm_Hr8, S_DwnTm_Hr9, S_DwnTm_Hr10 As Double
Dim S_DwnTm_Min1, S_DwnTm_Min2, S_DwnTm_Min3, S_DwnTm_Min4, S_DwnTm_Min5 As Double
Dim S_DwnTm_Min6, S_DwnTm_Min7, S_DwnTm_Min8, S_DwnTm_Min9, S_DwnTm_Min10 As Double
Dim S_Total_Downtime As Double
'******** Start Verify only Numbers in the Hours and Minutes Fields**************************
If ((IsNumeric(Sort_DwnTm_Hr1) Or Sort_DwnTm_Hr1 = "") And (IsNumeric(Sort_DwnTm_Hr2) Or Sort_DwnTm_Hr2 = "") And _
(IsNumeric(Sort_DwnTm_Hr3) Or Sort_DwnTm_Hr3 = "") And (IsNumeric(Sort_DwnTm_Hr4) Or Sort_DwnTm_Hr4 = "") And _
(IsNumeric(Sort_DwnTm_Hr5) Or Sort_DwnTm_Hr5 = "") And (IsNumeric(Sort_DwnTm_Hr6) Or Sort_DwnTm_Hr6 = "") And _
(IsNumeric(Sort_DwnTm_Hr7) Or Sort_DwnTm_Hr7 = "") And (IsNumeric(Sort_DwnTm_Hr8) Or Sort_DwnTm_Hr8 = "") And _
(IsNumeric(Sort_DwnTm_Hr9) Or Sort_DwnTm_Hr9 = "") And (IsNumeric(Sort_DwnTm_Hr10) Or Sort_DwnTm_Hr10 = "") And _
(IsNumeric(Sort_DwnTm_Min1) Or Sort_DwnTm_Min1 = "") And (IsNumeric(Sort_DwnTm_Min2) Or Sort_DwnTm_Min2 = "") And _
(IsNumeric(Sort_DwnTm_Min3) Or Sort_DwnTm_Min3 = "") And (IsNumeric(Sort_DwnTm_Min4) Or Sort_DwnTm_Min4 = "") And _
(IsNumeric(Sort_DwnTm_Min5) Or Sort_DwnTm_Min5 = "") And (IsNumeric(Sort_DwnTm_Min6) Or Sort_DwnTm_Min6 = "") And _
(IsNumeric(Sort_DwnTm_Min7) Or Sort_DwnTm_Min7 = "") And (IsNumeric(Sort_DwnTm_Min8) Or Sort_DwnTm_Min8 = "") And _
(IsNumeric(Sort_DwnTm_Min9) Or Sort_DwnTm_Min9 = "") And (IsNumeric(Sort_DwnTm_Min10) Or Sort_DwnTm_Min10 = "")) Then
Else
MsgBox ("Downtime entry is not a number, review and try again"), vbExclamation
Exit Sub
End If
'******** End Verify only Numbers in the Hours and Minutes Fields **************************
'******** Start Verify No downtime where there is no equipment listed **************************
If (((Sort_DwnTm_Hr1 <> "" Or Sort_DwnTm_Min1 <> "") And Sort_DwnTm_Equip1 = "") And ((Sort_DwnTm_Hr2 <> "" Or Sort_DwnTm_Min2 <> "") And Sort_DwnTm_Equip2 = "") And _
((Sort_DwnTm_Hr3 <> "" Or Sort_DwnTm_Min3 <> "") And Sort_DwnTm_Equip3 = "") And ((Sort_DwnTm_Hr4 <> "" Or Sort_DwnTm_Min4 <> "") And Sort_DwnTm_Equip4 = "") And _
((Sort_DwnTm_Hr5 <> "" Or Sort_DwnTm_Min5 <> "") And Sort_DwnTm_Equip5 = "") And ((Sort_DwnTm_Hr6 <> "" Or Sort_DwnTm_Min6 <> "") And Sort_DwnTm_Equip6 = "") And _
((Sort_DwnTm_Hr7 <> "" Or Sort_DwnTm_Min7 <> "") And Sort_DwnTm_Equip7 = "") And ((Sort_DwnTm_Hr8 <> "" Or Sort_DwnTm_Min8 <> "") And Sort_DwnTm_Equip8 = "") And _
((Sort_DwnTm_Hr9 <> "" Or Sort_DwnTm_Min9 <> "") And Sort_DwnTm_Equip9 = "") And ((Sort_DwnTm_Hr10 <> "" Or Sort_DwnTm_Min10 <> "") And Sort_DwnTm_Equip10 = "")) Then
MsgBox ("Equipment is Blank and has Downtime Associated"), vbExclamation
Exit Sub
End If
'******** End Verify No downtime where there is no Equipment listed **************************
'******** Start Verify No downtime where there is no Issue listed **************************
If (((Sort_DwnTm_Hr1 <> "" Or Sort_DwnTm_Min1 <> "") And Sort_DwnTm_Issue1 = "") And ((Sort_DwnTm_Hr2 <> "" Or Sort_DwnTm_Min2 <> "") And Sort_DwnTm_Issue2 = "") And _
((Sort_DwnTm_Hr3 <> "" Or Sort_DwnTm_Min3 <> "") And Sort_DwnTm_Issue3 = "") And ((Sort_DwnTm_Hr4 <> "" Or Sort_DwnTm_Min4 <> "") And Sort_DwnTm_Issue4 = "") And _
((Sort_DwnTm_Hr5 <> "" Or Sort_DwnTm_Min5 <> "") And Sort_DwnTm_Issue5 = "") And ((Sort_DwnTm_Hr6 <> "" Or Sort_DwnTm_Min6 <> "") And Sort_DwnTm_Issue6 = "") And _
((Sort_DwnTm_Hr7 <> "" Or Sort_DwnTm_Min7 <> "") And Sort_DwnTm_Issue7 = "") And ((Sort_DwnTm_Hr8 <> "" Or Sort_DwnTm_Min8 <> "") And Sort_DwnTm_Issue8 = "") And _
((Sort_DwnTm_Hr9 <> "" Or Sort_DwnTm_Min9 <> "") And Sort_DwnTm_Issue9 = "") And ((Sort_DwnTm_Hr10 <> "" Or Sort_DwnTm_Min10 <> "") And Sort_DwnTm_Issue10 = "")) Then
MsgBox ("Equipment is Blank and has Downtime Associated"), vbExclamation
Exit Sub
End If
'******** End Verify No downtime where there is no Issue listed **************************
' ******** Start adding Downtime hours to Minutes and Calculate Hours down **********************
'*****************Had to convert Form fields to Double for calculations ******************
If (Sort_DwnTm_Equip1 = "") Then
Sort_DwnTm_Sum1 = ""
Else
S_DwnTm_Sum1 = Round((CDbl(Sort_DwnTm_Hr1) + (CDbl(Sort_DwnTm_Min1) / 60)), 2)
Sort_DwnTm_Sum1 = S_DwnTm_Sum1
End If
If (Sort_DwnTm_Equip2 = "") Then
Sort_DwnTm_Sum2 = ""
Else
S_DwnTm_Sum2 = Round((CDbl(Sort_DwnTm_Hr2) + (CDbl(Sort_DwnTm_Min2) / 60)), 2)
Sort_DwnTm_Sum2 = S_DwnTm_Sum2
End If
