When I click on the commandbutton (cmdArtToevoegen) the code does add the new articles to my sheet and shows me the form (added, in stead of MsgBox) correctly.
But my listbox (lstboxArt) on the form (frmArtikels) is acting strange (shows only 3 or 4 entries) and reboots excel.
What is wrong with my code?
Code
Private Sub cmdArtToevoegen_Click()
'*********************************************************************
'Deze code zoekt het laatste getal beginnend met 1, 2, 3 enz ...
'Het voegt automatisch een volgend nummer in
'als het laatste getal 1078 is, dan zal het een nieuwe "row" invoegen met nummer 1079
'*************************************************************************
On Error Resume Next
If optBrood.Value = False And optBroodDiepvries.Value = False And optBoterkoeken.Value = False _
And optPatisserie.Value = False And optTraiteur.Value = False Then
MsgBox "Gelieve een afdeling aan te duiden!", vbCritical, "Afdeling aanduiden"
Exit Sub
End If
If txtArtBenaming.Value = "" Then
MsgBox "Gelieve een artikelbenaming in te vullen!", vbCritical, "Afdeling aanduiden"
Exit Sub
End If
'*************************************************************************
Dim X As Variant
Dim i As Variant
If optBrood.Value = True Then
With Sheets("Producten_nieuw")
X = .Cells(2).CurrentRegion
For i = 2 To UBound(X)
If X(i, 2) > 1999 Then Exit For
Next
.Rows(i).Insert
.Cells(i, 2) = X(i - 1, 2) + 1 'kolom "B" (cells (i, 2)
.Cells(i, 3) = txtArtBenaming.Value 'kolom "C" (cells (i, 3)
.Cells(i, 4) = "Brood" 'kolom "D" (cells (i, 4)
End With
ElseIf optBroodDiepvries.Value = True Then
With Sheets("Producten_nieuw")
X = .Cells(2).CurrentRegion
For i = 2 To UBound(X)
If X(i, 2) > 2999 Then Exit For
Next
.Rows(i).Insert
.Cells(i, 2) = X(i - 1, 2) + 1
.Cells(i, 3) = txtArtBenaming.Value
.Cells(i, 4) = "Brood diepvries"
End With
ElseIf optBoterkoeken.Value = True Then
With Sheets("Producten_nieuw")
X = .Cells(2).CurrentRegion
For i = 2 To UBound(X)
If X(i, 2) > 3999 Then Exit For
Next
.Rows(i).Insert
.Cells(i, 2) = X(i - 1, 2) + 1
.Cells(i, 3) = txtArtBenaming.Value
.Cells(i, 4) = "Boterkoeken"
End With
ElseIf optPatisserie.Value = True Then
With Sheets("Producten_nieuw")
X = .Cells(2).CurrentRegion
For i = 2 To UBound(X)
If X(i, 2) > 4999 Then Exit For
Next
.Rows(i).Insert
.Cells(i, 2) = X(i - 1, 2) + 1
.Cells(i, 3) = txtArtBenaming.Value
.Cells(i, 4) = "Patisserie"
End With
ElseIf optTraiteur.Value = True Then
With Sheets("Producten_nieuw")
X = .Cells(2).CurrentRegion
For i = 2 To UBound(X)
If X(i, 2) > 5999 Then Exit For
Next
.Rows(i).Insert
.Cells(i, 2) = X(i - 1, 2) + 1
.Cells(i, 3) = txtArtBenaming.Value
.Cells(i, 4) = "Traiteur"
End With
End If
'****************************************************
frmArtToegevoegd.Show 'msgbox Art added
Call M_ClearAll.ClearAll
End Sub
Display More
Thx in advance for any help.