VBA: Fout 9 tijdens uitvoering; het subscript valt buiten het bereik
Ik krijg steeds bovenstaande foutmelding bij het script in VBA - Excell, kan niet vinden waar mijn fout is??????
Dits het bestand. Ik weet niet wat er juist verkeerd is in het bestand bij formulier : Userform1
De bedoeling van dit formulier is dat ik via de commandoknop"Zoeken" in sheet "Home" het scherm krijg waar ik a.d.h.v. 2 Comboboxen dat terug krijg uit sheet"DMS".
Via de eerste combobox zoek ik op naam van de klant en via de 2de combobox op Referte ui het sheet DMS, via de knop "Search" zou dan in het formulier de velden moeten ingevuld worden die ik nodig heb.
Ik krijg steeds bovenstaande foutmelding bij het script in VBA - Excell, kan niet vinden waar mijn fout is??????
Code:
Code VBA:
Option Explicit
Dim blnNew As Boolean
Dim TRows, i As Long
Private Sub cmdClose_Click()
If cmdClose.Caption = "Close" Then
Unload Me
Else
cmdClose.Caption = "Close"
cmdNew.Enabled = True
End If
End Sub
Private Sub cmdNew_Click()
blnNew = True
txtklant.Text = ""
txttype.Text = ""
txtdossier.Text = ""
txtdatum.Text = ""
txtbestand.Text = ""
txtbestemming.Text = ""
cmdClose.Caption = "Cancel"
cmdNew.Enabled = False
cmdSave.Enabled = True
Frame2.Enabled = True
End Sub
Private Sub cmdSave_Click()
If Trim(txtklant.Text) = "" Then
MsgBox "Enter klant", vbCritical, "Save"
txtklant.SetFocus
Exit Sub
End If
Call prSave
cmdClose.Caption = "Close"
cmdNew.Enabled = True
ThisWorkbook.Save
End Sub
Private Sub prSave()
''''' Save the DMS
If blnNew = True Then
TRows = Worksheets("DMS").Range("A1").CurrentRegion.Rows.Count
With Worksheets("DMS").Range("A1")
.Offset(TRows, 0).Value = txtklant.Text
.Offset(TRows, 1).Value = txttype.Text
.Offset(TRows, 2).Value = txtdossier.Text
.Offset(TRows, 3).Value = txtdatum.Text
.Offset(TRows, 4).Value = txtbestand.Text
.Offset(TRows, 11).Value = txtbestemming.Text
End With
txtklant.Text = ""
txttype.Text = ""
txtdossier.Text = ""
txtdatum.Text = ""
txtbestand.Text = ""
txtbestemming.Text = ""
Call prComboBoxFill
Else
For i = 2 To TRows
If Trim(Worksheets("DMS").Cells(i, 1).Value) = Trim(ComboBox3.Text) Then
Worksheets("DMS").Cells(i, 1).Value = txtklant.Text
Worksheets("DMS").Cells(i, 2).Value = txttype.Text
Worksheets("DMS").Cells(i, 3).Value = txtdossier.Text
Worksheets("DMS").Cells(i, 4).Value = txtdatum.Text
Worksheets("DMS").Cells(i, 5).Value = txtbestand.Text
Worksheets("DMS").Cells(i, 13).Value = txtbestemming.Text
txtklant.Text = ""
txttype.Text = ""
txtdossier.Text = ""
txtdatum.Text = ""
txtbestand.Text = ""
txtbestemming.Text = ""
Exit For
End If
Next i
End If
blnNew = False
If Trim(txtklant.Text) = "" Then
cmdSave.Enabled = False
Frame2.Enabled = False
Else
cmdSave.Enabled = True
Frame2.Enabled = True
End If
End Sub
Private Sub cmdSearch_Click()
blnNew = False
txtklant.Text = ""
txttype.Text = ""
txtdossier.Text = ""
txtdatum.Text = ""
txtbestand.Text = ""
txtbestemming.Text = ""
TRows = Worksheets("DMS").Range("A1").CurrentRegion.Rows.Count
If OptionButton1.Value = True Then
For i = 2 To TRows
If Trim(Worksheets("DMS").Cells(i, 1).Value) = Trim(ComboBox2.Text) Then
txtklant.Text = Worksheets("DMS").Cells(i, 1).Value
txttype.Text = Worksheets("DMS").Cells(i, 2).Value
txtdossier.Text = Worksheets("DMS").Cells(i, 3).Value
txtdatum.Text = Worksheets("DMS").Cells(i, 4).Value
txtbestand.Text = Worksheets("DMS").Cells(i, 5).Value
txtbestemming.Text = Worksheets("DMS").Cells(i, 12).Value
Exit For
End If
Next i
Else
For i = 2 To TRows
If Trim(Worksheets("DMS").Cells(i, 2).Value) = Trim(ComboBox3.Text) Then
txtklant.Text = Worksheets("DMS").Cells(i, 1).Value
txttype.Text = Worksheets("DMS").Cells(i, 2).Value
txtdossier.Text = Worksheets("DMS").Cells(i, 3).Value
txtdatum.Text = Worksheets("DMS").Cells(i, 4).Value
txtbestand.Text = Worksheets("DMS").Cells(i, 5).Value
txtbestemming.Text = Worksheets("DMS").Cells(i, 12).Value
Exit For
End If
Next i
End If
If Trim(txtklant.Text) = "" Then
cmdSave.Enabled = False
Frame2.Enabled = False
Else
cmdSave.Enabled = True
Frame2.Enabled = True
End If
End Sub
Private Sub ComboBox2_Change()
End Sub
Private Sub ComboBox3_Change()
End Sub
Private Sub Frame2_Click()
End Sub
Private Sub Label1_Click()
End Sub
Private Sub Label2_Click()
End Sub
Private Sub Label3_Click()
End Sub
Private Sub OptionButton1_Click()
ComboBox2.Enabled = True
ComboBox3.Enabled = False
End Sub
Private Sub OptionButton2_Click()
ComboBox2.Enabled = False
ComboBox3.Enabled = True
End Sub
Private Sub prComboBoxFill()
TRows = Worksheets("DMS").Range("A1").CurrentRegion.Rows.Count
ComboBox2.Clear
For i = 2 To TRows
ComboBox2.AddItem Worksheets("DMS").Cells(i, 1).Value
Next i
End Sub
Private Sub prComboBoxFill2()
TRows = Worksheets("DMS").Range("A1").CurrentRegion.Rows.Count
ComboBox3.Clear
For i = 2 To TRows
ComboBox3.AddItem Worksheets("DMS").Cells(i, 2).Value
Next i
End Sub
Private Sub txtdatum_Change()
End Sub
Private Sub txtklant_Change()
End Sub
Private Sub txttype_Change()
End Sub
Private Sub UserForm_Initialize()
Call prComboBoxFill
Call prComboBoxFill2
cmdSave.Enabled = False
Frame2.Enabled = False
OptionButton1.Value = True
Dim wb As Workbook: Set wb = ThisWorkbook
Dim WS As Worksheet
Dim LastRow As Long
Dim aCell As Range
Set WS = wb.Sheets("Type DMS")
With WS
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
For Each aCell In .Range("C1:C" & LastRow)
If aCell.Value <> "" Then
Me.txttype.AddItem aCell.Value
End If
Next
End With
Set WS = wb.Sheets("Type DMS")
With WS
LastRow = .Cells(.Rows.Count, "O").End(xlUp).Row
For Each aCell In .Range("O1:O" & LastRow)
If aCell.Value <> "" Then
Me.txtbestand.AddItem aCell.Value
End If
Next
End With
End Sub
Dits het bestand. Ik weet niet wat er juist verkeerd is in het bestand bij formulier : Userform1
De bedoeling van dit formulier is dat ik via de commandoknop"Zoeken" in sheet "Home" het scherm krijg waar ik a.d.h.v. 2 Comboboxen dat terug krijg uit sheet"DMS".
Via de eerste combobox zoek ik op naam van de klant en via de 2de combobox op Referte ui het sheet DMS, via de knop "Search" zou dan in het formulier de velden moeten ingevuld worden die ik nodig heb.
Bijlagen
Laatst bewerkt: