Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Dim iRow As Long
Dim ws As Worksheet
Dim lrow As Long
Dim lCount As Long
Select Case MsgBox("Gegevens opslaan?", vbYesNoCancel, "Formulier sluiten")
Case vbYes
Set ws = Worksheets("Blad1")
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData
Range("B5").Select
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
If Trim(Me.TextBox1.Value) = "" Then
Me.TextBox1.SetFocus
MsgBox "Nummer invullen", vbOKOnly + vbInformation, "Nummer"
Exit Sub
End If
ws.Cells(iRow, 1).Value = Me.TextBox1.Value
ws.Cells(iRow, 2).Value = Me.ComboBox2.Value
ws.Cells(iRow, 3).Value = Me.ComboBox3.Value
ws.Cells(iRow, 4).Value = Me.TextBox4.Value
ws.Cells(iRow, 5).Value = Me.TextBox5.Value
ws.Cells(iRow, 6).Value = Me.TextBox6.Value
ws.Cells(iRow, 7).Value = Me.TextBox7.Value
ws.Cells(iRow, 8).Value = Me.ComboBox8.Value
ws.Cells(iRow, 9).Value = Me.TextBox9.Value
ws.Cells(iRow, 10).Value = Me.TextBox10.Value
ws.Cells(iRow, 11).Value = Me.TextBox11.Value
ws.Cells(iRow, 12).Value = Me.TextBox12.Value
ws.Cells(iRow, 13).Value = Me.TextBox13.Value
ws.Cells(iRow, 14).Value = Me.ComboBox14.Value
ws.Cells(iRow, 15).Value = Me.TextBox15.Value
ws.Cells(iRow, 16).Value = Me.TextBox16.Value
ws.Cells(iRow, 17).Value = Me.CheckBox17.Value
ws.Cells(iRow, 18).Value = Me.TextBox18.Value
ws.Cells(iRow, 19).Value = Me.Label1.Caption
ws.Cells(iRow, 20).Value = Me.Label2.Caption
ws.Cells(iRow, 21).Value = Me.Label3.Caption
ws.Cells(iRow, 22).Value = Me.Label4.Caption
ws.Cells(iRow, 23).Value = Me.Label5.Caption
ws.Cells(iRow, 24).Value = Me.Label6.Caption
ws.Cells(iRow, 25).Value = Me.Label7.Caption
ws.Cells(iRow, 26).Value = Me.TextBox33.Value
ws.Cells(iRow, 27).Value = Me.TextBox34.Value
ws.Cells(iRow, 28).Value = Me.ComboBox35.Value
ws.Cells(iRow, 29).Value = Me.ComboBox36.Value
ws.Cells(iRow, 30).Value = Me.ComboBox37.Value
ws.Cells(iRow, 31).Value = Me.CheckBox38.Value
ws.Cells(iRow, 32).Value = Me.TextBox39.Value
ws.Cells(iRow, 33).Value = Me.TextBox40.Value
ws.Cells(iRow, 34).Value = Me.TextBox41.Value
TextBox1.Enabled = True
Me.TextBox1.SetFocus
TextBox1.Enabled = False
ActiveWorkbook.Worksheets("Blad1").ListObjects("Tabel1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Blad1").ListObjects("Tabel1").Sort.SortFields.Add _
Key:=Range("Tabel1[[#All],[Nummer]]"), SortOn:=xlSortOnValues _
, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Blad1").ListObjects("Tabel1").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
For lrow = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
lCount = lCount + 1
If Cells(lrow, "A") = Cells(lrow, "A").Offset(-1, 0) Then
Cells(lrow, "A").Offset(-1, 0).EntireRow.Delete
End If
Next lrow
Range("A1").Select
ActiveSheet.Protect Password:="0000", DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
Case vbNo
Case vbCancel
Cancel = True
End Select
End Sub