Private Sub CommandButton1_Click() 'Voorraad aanvullen
Dim Lijst As Integer
If ListBox1.ListIndex = -1 Then
MsgBox "Maak uw keuze in de lijst", vbExclamation
Exit Sub
End If
If TextBox4.Value = "" Then
MsgBox "Aantal ingeven aub.", vbExclamation
TextBox4.SetFocus
Exit Sub
End If
Lijst = ListBox1.ListIndex + 3
Cells(Lijst, 3) = (Val(TextBox3.Value) + Val(TextBox4.Value))
Cells(Lijst, 6) = CDate(TextBox5.Value)
MsgBox "Ingave is aangepast"
ListBox1.List = Sheets("Voorraad").Range("A3:E" & [A65536].End(3).Row).Value
End Sub
Private Sub CommandButton2_Click() 'voorraad aanvullen
Dim Lijst As Integer
If ListBox1.ListIndex = -1 Then
MsgBox "Maak uw keuze in de lijst", vbExclamation
Exit Sub
End If
If TextBox4.Value = "" Then
MsgBox "Aantal ingeven aub.", vbExclamation
TextBox4.SetFocus
Exit Sub
End If
Lijst = ListBox1.ListIndex + 3
Cells(Lijst, 3) = (Val(TextBox3.Value) - Val(TextBox4.Value))
Cells(Lijst, 6) = CDate(TextBox5.Value)
MsgBox "Ingave is aangepast"
ListBox1.List = Sheets("Voorraad").Range("A3:E" & [A65536].End(3).Row).Value
End Sub
Private Sub CommandButton3_Click() 'Sluiten
Unload Me
End Sub
Private Sub CommandButton4_Click() 'leegmaken zoek balk
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
TextBox6.Value = ""
TextBox7.Value = ""
TextBox21.Value = ""
TextBox6.SetFocus
End Sub
Private Sub CommandButton5_Click() ,in bestellijst zetten
Dim LastRow As Long, ws As Worksheet
Set ws = Blad3
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1
ws.Range("A" & LastRow).Value = TextBox1.Text
ws.Range("B" & LastRow).Value = TextBox2.Text
ws.Range("C" & LastRow).Value = TextBox3.Text
ws.Range("D" & LastRow).Value = TextBox7.Text
ws.Range("E" & LastRow).Value = TextBox5.Text
End Sub
Private Sub ListBox1_Click()
Dim rngMyData As Range
TextBox1.Value = Me.ListBox1.Column(0)
TextBox2.Value = Me.ListBox1.Column(1)
TextBox3.Value = Me.ListBox1.Column(2)
TextBox7.Value = Me.ListBox1.Column(3)
TextBox21.Value = Me.ListBox1.Column(4)
Set rngMyData = Sheets("Voorraad").Columns("A")
TextBox4.Value = ""
If (Val(TextBox3.Value) <= Val(TextBox7.Value)) Then
Frame3.Visible = True
Else
Frame3.Visible = False
End If
End Sub
Private Sub TextBox6_Change()
Dim sFind As String
sFind = Me.TextBox6.Text
If Len(sFind) = 0 Then
Me.ListBox1.ListIndex = -1
Me.ListBox1.TopIndex = 0
Else
For i = 0 To Me.ListBox1.ListCount - 1
If InStr(UCase(ListBox1.List(i)), UCase(sFind)) > 0 Then
Me.ListBox1.TopIndex = i
Me.ListBox1.ListIndex = i
Exit For
End If
Next i
End If
End Sub
Private Sub UserForm_Initialize()
TextBox5.Value = Format(Date, "dd/mm/yyyy")
ListBox1.List = Sheets("Voorraad").Range("A3:E" & [A65536].End(3).Row).Value
TextBox6.SetFocus
With Sheets("Leveranciers")
ComboBox1.List = Application.Transpose(.Range("A2").Resize(Application.CountA(.Columns(1)) - 1))
End With
End Sub
Private Sub CommandButton6_Click() 'Nieuw artikel toevoegen
Dim n As Integer
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Voorraad")
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Dim FindString As String
Dim Rng As Range
FindString = ComboBox1.Value
If Trim(FindString) <> "" Then
With Sheets("Leveranciers").Range("A:A")
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Else
MsgBox "Deze leverancier bestaat niet, gelieve eerst deze leverancier aan te maken!!", vbCritical, "Niet gekende leverancier"
End If
End With
'Exit Sub ''Hier stopte de macro
TextBox16.SetFocus
End If
For n = 8 To 9
If Me("TextBox" & n).Value = "" Then
MsgBox "Alle gegevens moeten ingevuld zijn"
Exit Sub
End If
Next n
For n = 11 To 15
If Me("TextBox" & n).Value = "" Then
MsgBox "Alle gegevens moeten ingevuld zijn"
Exit Sub
End If
Next n
If Me.ComboBox1.Value = "" Then
MsgBox "Alle gegevens moeten ingevuld zijn"
Exit Sub
End If
Application.ScreenUpdating = False
ws.Cells(iRow, 1).Value = Me.TextBox8.Value
ws.Cells(iRow, 2).Value = Me.TextBox9.Value
ws.Cells(iRow, 3).Value = Me.TextBox14.Value
ws.Cells(iRow, 4).Value = Me.TextBox15.Value
ws.Cells(iRow, 5).FormulaR1C1 = "=RC[5]*120%" 'aanpassen formule verkoop prijs
ws.Cells(iRow, 6).Value = CDate(TextBox5.Value)
ws.Cells(iRow, 7).Value = Me.ComboBox1.Value
ws.Cells(iRow, 8).Value = Me.TextBox11.Value
ws.Cells(iRow, 9).Value = Me.TextBox12.Value
ws.Cells(iRow, 10).Value = Me.TextBox13.Value
With ws.ListObjects("Tabel1").Sort
.SortFields.Clear
'.SortFields.Add2 Key:=Range("Tabel1[Artikel nr.]")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Me.TextBox8.Value = ""
Me.TextBox9.Value = ""
Me.ComboBox1.Value = ""
Me.TextBox11.Value = ""
Me.TextBox12.Value = ""
Me.TextBox13.Value = ""
Me.TextBox14.Value = ""
Me.TextBox15.Value = ""
ListBox1.List = Sheets("Voorraad").Range("A3:E" & [A65536].End(3).Row).Value
Application.ScreenUpdating = True
End Sub
Private Sub CommandButton7_Click() 'Leverancier toevoegen
Dim n As Integer
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Leveranciers")
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
For n = 16 To 20
If Me("TextBox" & n).Value = "" Then
MsgBox "Alle gegevens moeten ingevuld zijn"
Exit Sub
End If
Next n
Application.ScreenUpdating = False
ws.Cells(iRow, 1).Value = Me.TextBox16.Value
ws.Cells(iRow, 2).Value = Me.TextBox17.Value
ws.Cells(iRow, 3).Value = Me.TextBox18.Value
ws.Cells(iRow, 4).Value = Me.TextBox19.Value
ws.Cells(iRow, 5).Value = Me.TextBox20.Value
ws.Range("A2:E" & ws.Cells(Rows.Count, 1).End(xlUp).Row).Sort ws.Range("A3"), xlAscending
ws.Range("A2:E" & ws.Cells(Rows.Count, 1).End(xlUp).Row).Borders.Weight = xlThin
ws.Range("A2:E" & ws.Cells(Rows.Count, 1).End(xlUp).Row).Columns.AutoFit
Me.TextBox16.Value = ""
Me.TextBox17.Value = ""
Me.TextBox18.Value = ""
Me.TextBox19.Value = ""
Me.TextBox20.Value = ""
Application.ScreenUpdating = True
End Sub