Thursday, November 5, 2020

Listbox advance Filter Userform Excel VBA

 Listbox advance Filter Userform Excel VBA


Listbox advance Filter Userform Excel VBA

Watch Video


VBA Code

Listbox Fill

Private Sub UserForm_Initialize()
Dim i As Long
Dim X As Integer
For i = 1 To Sheet1.Range("A1000000").End(xlUp).Row
Me.ListBox1.AddItem
For X = 1 To 7
Me.ListBox1.List(i - 1, X - 1) = Sheet1.Cells(i, X)
Next X
Next i
End Sub

Combobox Fill Without Duplicate Value

Private Sub UserForm_Activate()
'Voucher Box Fill
Dim i As Long  'Cobobox fill   Voucher
Dim VCH As New Collection
Dim Mylist As String
For i = 2 To Sheet1.Range("A1000000").End(xlUp).Row
On Error Resume Next
VCH.Add Sheet1.Cells(i, "A"), Sheet1.Cells(i, "A")
Next i
For Each ITM In VCH
Me.VCHBox.AddItem ITM
Next ITM

'Name Box Fill
Dim X As Long
Dim NME As New Collection     'Cobobox fill   Namebox
For X = 2 To Sheet1.Range("A1000000").End(xlUp).Row
NME.Add Sheet1.Cells(X, "B"), Sheet1.Cells(X, "B")
Next X
For Each NIT In NME
Me.NAMEBox.AddItem NIT
Next NIT

'Admin Box Fill
Dim c As Long
Dim ADM As New Collection      'Cobobox fill Admin
For c = 2 To Sheet1.Range("A1000000").End(xlUp).Row
ADM.Add Sheet1.Cells(c, "C"), Sheet1.Cells(c, "C")
Next c

For Each MIT In ADM
Me.ADMNBox.AddItem MIT
Next MIT

End Sub


'Combobox  Activate

Private Sub VCHBox_Change()
Dim i, X As Integer
Dim Mylist As String
With Me.ListBox1
For i = 1 To .ListCount - 1
For X = i To .ListCount - 1
If .List(X, 0) = Me.VCHBox Then
For c = 0 To 6
Mylist = .List(X, c)
.List(X, c) = .List(i, c)
.List(i, c) = Mylist
Me.ListBox1.Selected(i) = True
Next c
End If
Next X
Next i

Dim M As Integer
Dim SUM, SUM1 As Double
For M = 1 To .ListIndex()
SUM = SUM + Val(.List(M, 3))
SUM1 = SUM1 + Val(.List(M, 4))
Next M
Me.TextBox1 = Format(SUM1, "#####.00")
Me.TextBox2 = Format(SUM, "#####.00")

End With
End Sub

Private Sub NAMEBox_Change()
Dim i, X As Integer
Dim Mylist As String
With Me.ListBox1
For i = 1 To .ListCount - 1
For X = i To .ListCount - 1
If .List(X, 1) = Me.NAMEBox Then
For c = 0 To 6
Mylist = .List(X, c)
.List(X, c) = .List(i, c)
.List(i, c) = Mylist
Me.ListBox1.Selected(i) = True
Next c
End If
Next X
Next i

Dim M As Integer
Dim SUM, SUM1 As Double
For M = 1 To .ListIndex()
SUM = SUM + Val(.List(M, 3))
SUM1 = SUM1 + Val(.List(M, 4))
Next M
Me.TextBox1 = Format(SUM1, "#####.00")
Me.TextBox2 = Format(SUM, "#####.00")

End With
End Sub


Private Sub ADMNBox_Change()
Dim i, X As Integer
Dim Mylist As String
With Me.ListBox1
For i = 1 To .ListCount - 1
For X = i To .ListCount - 1
If .List(X, 2) = Me.ADMNBox Then
For c = 0 To 6
Mylist = .List(X, c)
.List(X, c) = .List(i, c)
.List(i, c) = Mylist
Me.ListBox1.Selected(i) = True
Next c
End If
Next X
Next i

Dim M As Integer
Dim SUM, SUM1 As Double
For M = 1 To .ListIndex()
SUM = SUM + Val(.List(M, 3))
SUM1 = SUM1 + Val(.List(M, 4))
Next M
Me.TextBox1 = Format(SUM1, "#####.00")
Me.TextBox2 = Format(SUM, "#####.00")

End With
End Sub