I have a Table containing 3 columns:
ArtNr:
Article:
Aantal:
I have a userform for searching an article in the table. Once found, I should be able to input a quantity for that article.
With a commandbutton, the quantity is entered into the table.
It should also show the entered line in a listbox on the form until the form is quit. It should show multiple lines, every line that contains a quantity.
When there is no quantity, it means it has not been ordered, so it should not be shown.
How can I show only the lines from the table in a listbox on the userform that are containing a quantity?
This is the code I have so far. The commandbuttom1 action is working but it shows all the rows in stead of only those who have a quantity filled in.
The rowsource should be unlimited in stead of now "A4:C125".
Private Sub cmdEnter_Click()
Dim artikel_Nr As String
artikel_Nr = Trim(txtArtNr.Text)
lastRow = Worksheets("Producten").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastRow
If Worksheets("Producten").Cells(i, 1).Value = artikel_Nr Then
Worksheets("Producten").Cells(i, 2).Value = txtArtikel.Text
Worksheets("Producten").Cells(i, 3).Value = txtAantal.Text
End If
Next
End Sub
Private Sub cmsExit_Click()
ActiveSheet.ShowAllData
Unload Me
End Sub
Private Sub CommandButton1_Click()
'Dim cell As Range
'Dim Rng As Range
'
'With Sheets("Producten")
'Set Rng = Range("C:C")
'End With
'
'For Each cell In Rng.Cells
'If cell.Value <> vbNullString Then
' Me.ListBox1.AddItem cell.Value
' Me.ListBox1.List(ListBox1.ListCount - 1, 1) = "bar"
' End If
'Next cell
Dim list As Object
Set list = frmIngave.Controls.Add("Forms.ListBox.1", "Bestelde artikelen", True)
'If Worksheets("Producten").Cells(i, 3).Value <> vbNullString Then
With list
.Top = 318
.Left = 35
.Width = 518
.Height = 220
.ColumnHeads = True
.ColumnCount = 3
.ColumnWidths = "50;130"
.MultiSelect = fmMultiSelectExtended
.RowSource = "Producten!a4:c125"
End With
'End If
End Sub
Private Sub optbegintmet_Click()
Call txtArtikel_Change
End Sub
Private Sub optbevat_Click()
Call txtArtikel_Change
End Sub
Private Sub txtArtikel_Change()
If txtArtikel.Value = "" Then
lblAfdeling.Caption = ""
txtArtNr.Value = ""
End If
txtAantal.Locked = True
'txtArtNr.Value = ""
If optBegintmet Then
ActiveSheet.ListObjects("Producten").Range.autofilter Field:=2, Criteria1:=txtArtikel.Value & "*"
Else
ActiveSheet.ListObjects("Producten").Range.autofilter Field:=2, Criteria1:="*" & txtArtikel.Value & "*"
End If
'ListObjects("Producten").Range.autofilter Field:=1, Criteria1:=Range("B1") & "*"
End Sub
Private Sub txtArtNr_Change()
Dim artikel_Nr As String
lblAfdeling.Visible = True
artikel_Nr = Trim(txtArtNr.Text)
lastRow = Worksheets("Producten").Cells(Rows.Count, 1).End(xlUp).Row
If txtArtNr.Value = "" Then
lblAfdeling.Caption = ""
txtArtikel.Value = ""
End If
For i = 2 To lastRow
If Worksheets("Producten").Cells(i, 1).Value = artikel_Nr Then
txtArtikel.Text = Worksheets("Producten").Cells(i, 2).Value
txtAantal.Text = Worksheets("Producten").Cells(i, 3).Value
If Worksheets("Producten").Cells(i, 5).Value = "x" Then
lblAfdeling.Caption = "Brood"
ElseIf Worksheets("Producten").Cells(i, 6).Value = "x" Then
lblAfdeling.Caption = "Brood diepvries"
ElseIf Worksheets("Producten").Cells(i, 7).Value = "x" Then
lblAfdeling.Caption = "Boterkoeken"
ElseIf Worksheets("Producten").Cells(i, 8).Value = "x" Then
lblAfdeling.Caption = "Patisserie"
ElseIf Worksheets("Producten").Cells(i, 9).Value = "x" Then
lblAfdeling.Caption = "Traiteur"
End If
End If
Next
txtAantal.Locked = False
'txtAantal.SetFocus = True
End Sub
Private Sub txtArtNr_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Frame4.SetFocus
End Sub
Private Sub UserForm_Activate()
cmdZoek.Visible = False
optBegintmet.Value = True
txtAantal.Locked = True
lblAfdeling.Visible = False
txtArtNr.SetFocus
End Sub
