PDA

View Full Version : Listbox.Column



HV_L
06-20-2019, 05:39 AM
Hi, I have this piece of code, then I had to move the "kosten_tbl" on the "sheet Basisgegevens"
First, the kosten_tbl was starting at A2 having 15 rows
Now I moved the kosten_tbl to the S column now I can't add items to the kosten_tbl (S1:S16) (and growing when needed)
Can someone help me out?
Thnx!

Private Sub Cmd_Toevoegen_Click()If Len(Trim(T_edit)) = 0 Then
MsgBox T_edit.Tag & " is niet ingevuld!", vbCritical, "Fout!"
T_edit.SetFocus
Exit Sub
End If
Set ws = Worksheets("Basisgegevens")
For i = 0 To LB_overzicht.ListCount - 1
If LB_overzicht.Column(0, i) = T_edit.Value Then
MsgBox "Deze kostenpost is reeds ingegeven.", vbCritical, "Fout"
Exit Sub
End If
Next i
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
ws.Cells(iRow, 1).Value = T_edit.Value
T_edit.Value = ""
LB_overzicht.List = [kosten_tbl].Value
End Sub

dotchiejack
06-20-2019, 07:41 AM
Like this?

ws.Cells(iRow, 19).Value = T_edit.Value
En voor de Nederlanders, een voorbeeldbestandje werkt beter:cool:

HV_L
06-20-2019, 08:14 AM
Hi,
I can add an new item now, although it is not visible in the table, and I get an error at the line:

If LB_overzicht.Column(0, i) = T_edit.Value Then

This is the complete code, (example file is easier, I know, but too complicated here, sorry..)



Private Sub Cmd_invoeg_Click()
Dim ws As Worksheet
Set ws = Worksheets("Data")
With ws
.Range("A527:E544").ClearContents
End With
With LB_select
r = 0
For i = 0 To .ListCount - 1
If .Selected(i) Then
ws.Cells(r + 527, 1).Value = .List(i)
r = r + 1
End If
Next i
End With
LB_select.List = [kosten_tbl].Value
End Sub


Private Sub Cmd_Toevoegen_Click()
If Len(Trim(T_edit)) = 0 Then
MsgBox T_edit.Tag & " is niet ingevuld!", vbCritical, "Fout!"
T_edit.SetFocus
Exit Sub
End If
Set ws = Worksheets("Basisgegevens")
For i = 0 To LB_overzicht.ListCount - 1
If LB_overzicht.Column(0, i) = T_edit.Value Then
MsgBox "Deze kostenpost is reeds ingegeven.", vbCritical, "Fout"
Exit Sub
End If
Next i
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
ws.Cells(iRow, 19).Value = T_edit.Value
T_edit.Value = ""
LB_overzicht.List = [kosten_tbl].Value
End Sub
Private Sub Cmd_verwijder_Click()
Set ws = Worksheets("Basisgegevens")
Set Rng = [kosten_tbl]
Set fnd = Rng.Find(What:=T_edit.Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
If LB_overzicht.ListIndex = -1 Then
MsgBox "Kies eerst een kostenpost.", vbCritical, "Ingave?"
LB_overzicht.SetFocus
Exit Sub
End If
If Not fnd Is Nothing Then smessage = "Kostenpost verwijderen, ben je zeker" + "?"
If MsgBox(smessage, vbQuestion + vbYesNo, "Bevestig verwijderen") = vbNo Then GoTo oops
ws.Rows(fnd.Row).Delete
oops:
Reset
End Sub
Private Sub Cmd_reset_Click()
Reset
End Sub
Private Sub Cmd_sluit_Click()
Unload Me
End Sub




Private Sub LB_overzicht_Click()
T_edit.Value = LB_overzicht.Column(0)
End Sub
Private Sub UserForm_Initialize()
LB_select.List = [kosten_tbl].Value
LB_overzicht.List = [kosten_tbl].Value
End Sub
Sub Reset()
T_edit.Value = ""
LB_select.List = [kosten_tbl].Value
LB_overzicht.ListIndex = -1
LB_overzicht.List = [kosten_tbl].Value
End Sub