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