Refresh listbox

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
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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Forum statistics

Threads
1,214,982
Messages
6,122,581
Members
449,089
Latest member
Motoracer88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top