inactiveUser214710
Board Regular
- Joined
- Apr 27, 2012
- Messages
- 171
Hi again to everyone
I have a listbox with a List as a datasource in the first userforn, and I have a second form to to correct errors in the dataset.
What I would like is that when I add and remove items from the List, the listbox updates itself automatically.
when I save and close it, the update is done in the dataset itself but not on the listbox.
I am unable to do this.
How to get it done.
Who can help me.
Thank you in advance. Thank you.
Below is my code of the two forms and module
I have a listbox with a List as a datasource in the first userforn, and I have a second form to to correct errors in the dataset.
What I would like is that when I add and remove items from the List, the listbox updates itself automatically.
when I save and close it, the update is done in the dataset itself but not on the listbox.
I am unable to do this.
How to get it done.
Who can help me.
Thank you in advance. Thank you.
Below is my code of the two forms and module
VBA Code:
Option Explicit
'UserF1
Private Sub cbo1_Change() 'Choose the month'data
Sheet1.Range("W2") = cbo1
Call FiltroCbo
End Sub
Private Sub CmdEdit_Click() 'Re_Edit data
With UserF2
On Error Resume Next
UserF2.lblIDOrder.Caption = Lst1.List(Lst1.ListIndex)
UserF2.txt1.Text = Lst1.Column(4, Lst1.ListIndex)
UserF2.txt2.Text = Lst1.Column(5, Lst1.ListIndex)
End With
UserF2.Show
Lst1.RowSource = ""
End Sub
Private Sub CmdDel_Click() 'Delete data
Dim i As Integer
If MsgBox("Are you sure you want to delete this row?", vbYesNo + vbQuestion, "Delete row") = vbYes Then
For i = 2 To Range("g10000").End(xlUp).Row
If Cells(i, 1) = Lst1.List(Lst1.ListIndex) Then
Rows(i).Select
Selection.Delete
Lst1.List = Sheets("sheet1").Range("a1:g10000").Value
End If
Next i
End If
End Sub
Private Sub Lst1_Click() 'To show data select in other listbox
Dim i As Long
With Lst2
.Clear
.AddItem
For i = 0 To 6
.List(.ListCount - 1, i) = Lst1.List(Lst1.ListIndex, i)
Next i
End With
End Sub
Private Sub UserForm_Initialize()
Dim Base As Range
Dim Nome As String
Dim Lh As Long
Me.cbo1.List = Array("JANEIRO", "FEVEREIRO", _
"MARÇO", "ABRIL", "MAIO", "JUNHO", "JULHO", "AGOSTO", _
"SETEMBRO", "OUTUBRO", "NOVEMBRO", "DEZEMBRO")
Lh = Sheet1.Range("A1").CurrentRegion.Rows.Count
Set Base = Sheet1.Range(Sheet1.Cells(2, 1), Sheet1.Cells(Lh, 7)) 'Sheet1.Range("A1").CurrentRegion
Nome = "'" & Sheet1.Name & "'!"
Lst1.ColumnCount = 7
Sheet1.Range("w2:x2").ClearContents 'clean filter advence
End Sub
'Userform2
Option Explicit
'UserF2
Private Sub CmdSave_Click() 'Save
Dim i As Integer
With UserF1.Lst1
For i = 2 To Range("g10000").End(xlUp).Row
If Cells(i, 1) = lblIDOrder.Caption Then
Cells(i, 4) = txt1.Value
Cells(i, 6) = txt2.Text
End If
Next i
End With
With UserF1
On Error Resume Next
UserF1.Lst1.List = Sheets("sheet1").Range("g10000").Value
UserF1.cbo1.Value = ""
' UserF1.Lst1.Clear
' UserF1.Lst2.Clear
End With
UserF1.Show
Unload Me
End Sub
''MODULO FiltroCbo
Option Explicit
Sub FiltroCbo()
Dim Base As Range
Dim Crt As Range
Dim Filtrada As Range
Dim Nome As String
Dim Lh As Long
Set Base = Sheet1.Range("A1").CurrentRegion
Set Crt = Sheet1.Range("W1:w2")
Base.AdvancedFilter xlFilterCopy, Crt, Sheet2.Range("A1:G1")
Lh = Sheet2.Range("A1").CurrentRegion.Rows.Count
Set Filtrada = Sheet2.Range(Sheet2.Cells(2, 1), Sheet2.Cells(Lh, 7))
Nome = "'" & Sheet2.Name & "'!"
UserF1.Lst1.RowSource = Nome & Filtrada.Address
UserF1.Lst1.ColumnHeads = True
If Lh = 1 Then
UserF1.Lst1.ColumnHeads = False
End If
End Sub