Untitled
unknown
plain_text
a year ago
8.3 kB
0
Indexable
Never
Private Sub CMDBTN_SAVE_Click()
Dim wb As Workbook
Dim datalastRow As Long
Dim mainInvlastRow As Long
Dim i As Integer, j As Integer, k As Integer
Dim matchedRow As Long
Dim matched As Boolean
Set wb = ThisWorkbook
datalastRow = WorksheetFunction.Max(4, wb.Sheets("Data").Cells(Rows.count, "A").End(xlUp).Row)
mainInvlastRow = wb.Sheets("MainInventory").Cells(wb.Sheets("MainInventory").Rows.count, "B").End(xlUp).Row
'Validate input for required fields
If Me.TXTBXSURATJALAN.Value = "" Or Me.CMBBXCUSTOMER.Value = "" Or Me.DTPicker1.Value = vbNull _
Or (Me.TXTBXTBG1.Value = "" And Me.TXTBXTBG2.Value = "" And Me.TXTBXTBG3.Value = "" _
And Me.TXTBXTBG4.Value = "" And Me.TXTBXTBG5.Value = "" And Me.TXTBXTBG6.Value = "" _
And Me.TXTBXTBG7.Value = "" And Me.TXTBXTBG8.Value = "" And Me.TXTBXTBG9.Value = "" _
And Me.TXTBXTBG10.Value = "" And Me.TXTBXTBG11.Value = "" And Me.TXTBXTBG12.Value = "" _
And Me.TXTBXTBG13.Value = "" And Me.TXTBXTBG14.Value = "" And Me.TXTBXTBG15.Value = "" _
And Me.TXTBXTBG16.Value = "" And Me.TXTBXTBG17.Value = "" And Me.TXTBXTBG18.Value = "" _
And Me.TXTBXTBG19.Value = "" And Me.TXTBXTBG20.Value = "" And Me.TXTBXTBG21.Value = "" _
And Me.TXTBXTBG22.Value = "" And Me.TXTBXTBG23.Value = "" And Me.TXTBXTBG24.Value = "" _
And Me.TXTBXTBG25.Value = "" And Me.TXTBXTBG26.Value = "" And Me.TXTBXTBG27.Value = "" _
And Me.TXTBXTBG28.Value = "" And Me.TXTBXTBG29.Value = "" And Me.TXTBXTBG30.Value = "" _
And Me.TXTBXTBG31.Value = "" And Me.TXTBXTBG32.Value = "" And Me.TXTBXTBG33.Value = "" _
And Me.TXTBXTBG34.Value = "" And Me.TXTBXTBG35.Value = "" And Me.TXTBXTBG36.Value = "" _
And Me.TXTBXTBG37.Value = "" And Me.TXTBXTBG38.Value = "" And Me.TXTBXTBG39.Value = "" _
And Me.TXTBXTBG40.Value = "") Then
Beep 'play a beep sound for error message
MsgBox "Harap isi semua kolom yang ada tanda ""*"" (bintang).", vbExclamation + vbOKOnly, "Error"
Exit Sub
End If
' Write data to the next empty row
wb.Sheets("Data").Range("A" & datalastRow + 1).Value = Me.DTPicker1.Value
wb.Sheets("Data").Range("B" & datalastRow + 1).Value = Me.TXTBXCATATAN.Value
wb.Sheets("Data").Range("C" & datalastRow + 1).Value = Me.TXTBXSURATJALAN.Value
wb.Sheets("Data").Range("D" & datalastRow + 1).Value = Me.CMBBXCUSTOMER.Value
wb.Sheets("Data").Range("E" & datalastRow + 1).Value = Me.TXTBXTBG1.Value
wb.Sheets("Data").Range("F" & datalastRow + 1).Value = Me.TXTBXTBG2.Value
wb.Sheets("Data").Range("G" & datalastRow + 1).Value = Me.TXTBXTBG3.Value
wb.Sheets("Data").Range("H" & datalastRow + 1).Value = Me.TXTBXTBG4.Value
wb.Sheets("Data").Range("I" & datalastRow + 1).Value = Me.TXTBXTBG5.Value
wb.Sheets("Data").Range("J" & datalastRow + 1).Value = Me.TXTBXTBG6.Value
wb.Sheets("Data").Range("K" & datalastRow + 1).Value = Me.TXTBXTBG7.Value
wb.Sheets("Data").Range("L" & datalastRow + 1).Value = Me.TXTBXTBG8.Value
wb.Sheets("Data").Range("M" & datalastRow + 1).Value = Me.TXTBXTBG9.Value
wb.Sheets("Data").Range("N" & datalastRow + 1).Value = Me.TXTBXTBG10.Value
wb.Sheets("Data").Range("O" & datalastRow + 1).Value = Me.TXTBXTBG11.Value
wb.Sheets("Data").Range("P" & datalastRow + 1).Value = Me.TXTBXTBG12.Value
wb.Sheets("Data").Range("Q" & datalastRow + 1).Value = Me.TXTBXTBG13.Value
wb.Sheets("Data").Range("R" & datalastRow + 1).Value = Me.TXTBXTBG14.Value
wb.Sheets("Data").Range("S" & datalastRow + 1).Value = Me.TXTBXTBG15.Value
wb.Sheets("Data").Range("T" & datalastRow + 1).Value = Me.TXTBXTBG16.Value
wb.Sheets("Data").Range("U" & datalastRow + 1).Value = Me.TXTBXTBG17.Value
wb.Sheets("Data").Range("V" & datalastRow + 1).Value = Me.TXTBXTBG18.Value
wb.Sheets("Data").Range("W" & datalastRow + 1).Value = Me.TXTBXTBG19.Value
wb.Sheets("Data").Range("X" & datalastRow + 1).Value = Me.TXTBXTBG20.Value
wb.Sheets("Data").Range("Y" & datalastRow + 1).Value = WorksheetFunction.CountA(Range("E" & datalastRow + 1 & ":Y" & datalastRow + 1))
wb.Sheets("Data").Range("AA" & datalastRow + 1).Value = Me.TXTBXTBG21.Value
wb.Sheets("Data").Range("AB" & datalastRow + 1).Value = Me.TXTBXTBG22.Value
wb.Sheets("Data").Range("AC" & datalastRow + 1).Value = Me.TXTBXTBG23.Value
wb.Sheets("Data").Range("AD" & datalastRow + 1).Value = Me.TXTBXTBG24.Value
wb.Sheets("Data").Range("AE" & datalastRow + 1).Value = Me.TXTBXTBG25.Value
wb.Sheets("Data").Range("AF" & datalastRow + 1).Value = Me.TXTBXTBG26.Value
wb.Sheets("Data").Range("AG" & datalastRow + 1).Value = Me.TXTBXTBG27.Value
wb.Sheets("Data").Range("AH" & datalastRow + 1).Value = Me.TXTBXTBG28.Value
wb.Sheets("Data").Range("AI" & datalastRow + 1).Value = Me.TXTBXTBG29.Value
wb.Sheets("Data").Range("AJ" & datalastRow + 1).Value = Me.TXTBXTBG30.Value
wb.Sheets("Data").Range("AK" & datalastRow + 1).Value = Me.TXTBXTBG31.Value
wb.Sheets("Data").Range("AL" & datalastRow + 1).Value = Me.TXTBXTBG32.Value
wb.Sheets("Data").Range("AM" & datalastRow + 1).Value = Me.TXTBXTBG33.Value
wb.Sheets("Data").Range("AM" & datalastRow + 1).Value = Me.TXTBXTBG34.Value
wb.Sheets("Data").Range("AO" & datalastRow + 1).Value = Me.TXTBXTBG35.Value
wb.Sheets("Data").Range("AP" & datalastRow + 1).Value = Me.TXTBXTBG36.Value
wb.Sheets("Data").Range("AQ" & datalastRow + 1).Value = Me.TXTBXTBG37.Value
wb.Sheets("Data").Range("AR" & datalastRow + 1).Value = Me.TXTBXTBG38.Value
wb.Sheets("Data").Range("AS" & datalastRow + 1).Value = Me.TXTBXTBG39.Value
wb.Sheets("Data").Range("AT" & datalastRow + 1).Value = Me.TXTBXTBG40.Value
wb.Sheets("Data").Range("AU" & datalastRow + 1).Value = WorksheetFunction.CountA(Range("AA" & datalastRow + 1 & ":AT" & datalastRow + 1))
'Clear the input fields on the userform
Me.TXTBXCATATAN.Value = ""
Me.TXTBXSURATJALAN.Value = ""
Me.CMBBXCUSTOMER.Value = ""
Me.TXTBXTBG1.Value = ""
Me.TXTBXTBG2.Value = ""
Me.TXTBXTBG3.Value = ""
Me.TXTBXTBG4.Value = ""
Me.TXTBXTBG5.Value = ""
Me.TXTBXTBG6.Value = ""
Me.TXTBXTBG7.Value = ""
Me.TXTBXTBG8.Value = ""
Me.TXTBXTBG9.Value = ""
Me.TXTBXTBG10.Value = ""
Me.TXTBXTBG11.Value = ""
Me.TXTBXTBG12.Value = ""
Me.TXTBXTBG13.Value = ""
Me.TXTBXTBG14.Value = ""
Me.TXTBXTBG15.Value = ""
Me.TXTBXTBG16.Value = ""
Me.TXTBXTBG17.Value = ""
Me.TXTBXTBG18.Value = ""
Me.TXTBXTBG19.Value = ""
Me.TXTBXTBG20.Value = ""
Me.TXTBXTBG21.Value = ""
Me.TXTBXTBG22.Value = ""
Me.TXTBXTBG23.Value = ""
Me.TXTBXTBG24.Value = ""
Me.TXTBXTBG25.Value = ""
Me.TXTBXTBG26.Value = ""
Me.TXTBXTBG27.Value = ""
Me.TXTBXTBG28.Value = ""
Me.TXTBXTBG29.Value = ""
Me.TXTBXTBG30.Value = ""
Me.TXTBXTBG31.Value = ""
Me.TXTBXTBG32.Value = ""
Me.TXTBXTBG33.Value = ""
Me.TXTBXTBG34.Value = ""
Me.TXTBXTBG35.Value = ""
Me.TXTBXTBG36.Value = ""
Me.TXTBXTBG37.Value = ""
Me.TXTBXTBG38.Value = ""
Me.TXTBXTBG39.Value = ""
Me.TXTBXTBG40.Value = ""
For i = 1 To 40
matched = False
If Me.Controls("TXTBXTBG" & i).Value <> "" Then
For j = 4 To mainInvlastRow
If wb.Sheets("MainInventory").Cells(j, "B").Value = Me.Controls("TXTBXTBG" & i).Value Then
matchedRow = j
matched = True
Application.Wait Now + TimeValue("0:00:01") ' pause for one second
MsgBox "Match found for " & Me.Controls("TXTBXTBG" & i).Value & " at row " & matchedRow
Exit For
End If
Next j
If matched Then
If i <= 20 Then
wb.Sheets("MainInventory").Cells(matchedRow, "D").Value = Me.CMBBXCUSTOMER.Value
Else
wb.Sheets("MainInventory").Cells(matchedRow, "D").Value = "Gudang"
End If
End If
End If
Next i
Beep 'play a beep sound for success message
MsgBox "Input Data Berhasil.", vbInformation + vbOKOnly, "Success" 'Display pop up message when data input success
End Sub