Private Sub cmdAdd_Click()
Application.EnableEvents = False
Application.ScreenUpdating = False
Dim iRow As Long
Dim ws As Worksheet
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
If Trim(Me.txtomsch.Value) = "" Then
Me.txtomsch.SetFocus
MsgBox "Vul AUB een omschrijving in"
Exit Sub
End If
If Trim(Me.txtproject.Value) = "" Then
Me.txtomsch.SetFocus
MsgBox "Vul AUB een projectnummer in"
Exit Sub
End If
If Trim(Me.txtMOS.Value) = "" Then
Me.txtomsch.SetFocus
MsgBox "Vul AUB een MOSnummer in"
Exit Sub
End If
If Trim(Me.txtfig.Value) = "" Then
Me.txtomsch.SetFocus
MsgBox "Vul AUB een figuur nummer in"
Exit Sub
End If
If Trim(Me.txtNSN.Value) = "" Then
Me.txtomsch.SetFocus
MsgBox "Vul AUB een NSN nummer in"
Exit Sub
End If
If Trim(Me.txtnaam.Value) = "" Then
Me.txtomsch.SetFocus
MsgBox "Vul AUB de benaming uit het boek in"
Exit Sub
End If
With ws
If txtNSN.Value <> "" And Application.CountIf(.Range(.Cells(3, 5), .Cells(.Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0).Row, 2)), txtNSN.Value) > 0 Then
MsgBox "Controleer het NSN even!, " & Chr(10) & Chr(10) & Me.txtNSN & Chr(10) & Chr(10) & " bestaat al in de database!", vbInformation
End If
End With
With ws
If txtSAP.Value <> "" And Application.CountIf(.Range(.Cells(2, 6), .Cells(.Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0).Row, 2)), txtSAP.Value) > 0 Then
MsgBox "Controleer het SAP nummer !, " & Chr(10) & Chr(10) & Me.txtSAP & Chr(10) & Chr(10) & " bestaat al in de database!", vbInformation
Me.txtomsch.Value = ""
Me.txtproject.Value = ""
Me.txtMOS.Value = ""
Me.txtfig.Value = ""
Me.txtNSN.Value = ""
Me.txtSAP.Value = ""
Me.txtnaam.Value = ""
Cancel = True
End If
End With
[mySort]
ws.Cells(iRow, 1).Value = Me.txtomsch.Value
ws.Cells(iRow, 2).Value = Me.txtproject.Value
ws.Cells(iRow, 3).Value = Me.txtMOS.Value
ws.Cells(iRow, 4).Value = Me.txtfig.Value
ws.Cells(iRow, 5).Value = Me.txtNSN.Value
ws.Cells(iRow, 6).Value = Me.txtSAP.Value
ws.Cells(iRow, 7).Value = Me.txtnaam.Value
Me.txtomsch.Value = ""
Me.txtproject.Value = ""
Me.txtMOS.Value = ""
Me.txtfig.Value = ""
Me.txtNSN.Value = ""
Me.txtSAP.Value = ""
Me.txtnaam.Value = ""
Me.txtomsch.SetFocus
'set format cel
ws.Cells(iRow, 2).NumberFormat = "000000"
ws.Cells(iRow, 3).NumberFormat = "0000-0000"
ws.Cells(iRow, 4).NumberFormat = "00000"
ws.Cells(iRow, 5).NumberFormat = "00-000-0000"
ws.Cells(iRow, 6).NumberFormat = "10000000000"
'rijen tellen en rang selecteren
RowCount = ws.Range("A3").CurrentRegion.Rows.Count
ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add Key:=Range("A3") _
, Sorton:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
x = "A3"
y = "G" & RowCount
With ws.Sort
.SetRange Range(x & ":" & y)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'ontdubbelen van gegevens
Dim z As Integer
For z = 3 To RowCount
For i = 1 To 5
If ws.Cells(z, 5) = "" Or _
ws.Cells(z, 5) = ws.Cells(z + i, 5) Or _
(ws.Cells(z, 6) = ws.Cells(z + i, 6) And _
ws.Cells(z, 6) <> "" And ws.Cells(z + i, 6) <> "") Then
ws.Rows(z).Delete
If ws.Cells(z + 1, 1) <> "" Then
z = z - 1
End If
Exit For
End If
Next i
Next z
einde:
[cmdClose_Click]
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub