• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

link total column in listbox with textbox

alhagag

New Member
here when i search brand in textbox1 show the data in listbox i also want show total value column 7 ( balance) in textbox2
 

Attachments

Fluff13

Active Member
How about
Code:
Private Sub TextBox1_Change()
If TextBox1.Value = "" Then ListBox1.Clear: Exit Sub
Dim x As Worksheet
Dim Tot As Double
         ListBox1.Clear
    k = 0
For Each x In ThisWorkbook.Worksheets
        ss = x.Cells(Rows.Count, 1).End(xlUp).Row
        For Each C In x.Range("B2:B" & ss)
            b = InStr(C, TextBox1)
            If b > 0 Then
                ListBox1.AddItem
                ListBox1.List(k, 0) = x.Cells(C.Row, 1).Value
                ListBox1.List(k, 1) = x.Cells(C.Row, 2).Value
                ListBox1.List(k, 2) = x.Cells(C.Row, 3).Value
                ListBox1.List(k, 3) = x.Cells(C.Row, 4).Value
                ListBox1.List(k, 4) = x.Cells(C.Row, 5).Value
                ListBox1.List(k, 5) = x.Cells(C.Row, 6).Value
                ListBox1.List(k, 6) = x.Cells(C.Row, 7).Value
                Tot = Tot + x.Cells(C.Row, 7).Value
                k = k + 1
            End If
        Next C

Next x
Me.TextBox2 = Tot
End Sub
 

Belleke

Active Member
Hi,
Like this?
Steps.
1. Find the brand you want with textbox 1
The brands that do not meet the search criteria will disappear from the list.
2. Select the brand in the listbox
3. In textbox 2 you see the total of the chosen brand.
See attached.
I use a real table with the name data_tbl
 

Attachments

alhagag

New Member
thank your effort but what i mean , look the data in sheet1 the row(a2,a3) the same brand when i write in textbox1 show twice in the listbox here i would one time in the listbox then the total 290 i hope to be clear
 

alhagag

New Member
brandtybeoriginimportexportbalance
1200R20G580THI30010290
LIKE THIS

IGNORE THE DATE AND SHOW ONE TIME IN LISTBOX
 

Fluff13

Active Member
How about
Code:
Dim UfDic As Object
Private Sub TextBox1_Change()
   Dim Ky As Variant
   Dim r As Long, c As Long
  
   If TextBox1.Value = "" Then ListBox1.Clear: Exit Sub
   ListBox1.Clear
   For Each Ky In UfDic.keys
      If InStr(1, Ky, Me.TextBox1.Value, 1) > 0 Then
         With Me.ListBox1
            .AddItem Ky
            For c = 1 To 5
               .List(r, c) = UfDic(Ky)(c)
            Next c
            r = r + 1
         End With
      End If
   Next
End Sub

Private Sub UserForm_Initialize()
   Dim Ws As Worksheet
   Dim Cl As Range
   Dim Tmp As Variant
  
   Set UfDic = CreateObject("scripting.dictionary")
   For Each Ws In Worksheets
      For Each Cl In Ws.Range("B2", Ws.Range("B" & Rows.Count).End(xlUp))
         If Not UfDic.exists(Cl.Value) Then
            UfDic.Add Cl.Value, Application.Index(Cl.Offset(, 1).Resize(, 5).Value, 1, 0)
         Else
            Tmp = UfDic(Cl.Value)
            Tmp(3) = Tmp(3) + Cl.Offset(, 3).Value
            Tmp(4) = Tmp(4) + Cl.Offset(, 4).Value
            Tmp(5) = Tmp(5) + Cl.Offset(, 5).Value
            UfDic(Cl.Value) = Tmp
         End If
      Next Cl
   Next Ws
End Sub
 

Marc L

Excel Ninja
No issue on my side using Fluff13's code within your attachment !​
Total result should better be in a Label instead of a TextBox …​
Pretty same as Fluff13 but with the total, replace all the UserForm1 code module with all these codelines :​
Code:
Option Compare Text
Dim oDic As Object

Private Sub TextBox1_Change()
        Dim V, W
        If oDic.Count = 0 Then Beep: Exit Sub
        ListBox1.Clear
        If TextBox1.Value = "" Then TextBox2.Value = "": Exit Sub
    With CreateObject("Scripting.Dictionary")
        For Each V In oDic.Keys
            If V Like TextBox1.Value & "*" Then .Item(V) = oDic(V): W = W + oDic(V)(1, 6)
        Next
            If .Count = 1 Then ListBox1.List = .Items()(0) Else If .Count > 1 Then ListBox1.List = Application.Index(.Items, 0)
           .RemoveAll
    End With
        TextBox2.Value = W
End Sub

Private Sub UserForm_Initialize()
        Dim Ws As Worksheet, Rg As Range, V, C%
        Set oDic = CreateObject("Scripting.Dictionary")
    For Each Ws In Worksheets
        For Each Rg In Ws.Range("B2", Ws.Cells(Ws.Rows.Count, 2).End(xlUp))
            If Rg.Value2 > "" Then
                    V = oDic(Rg.Value2)
                If IsArray(V) Then
                    For C = 4 To 6:  V(1, C) = V(1, C) + Rg(1, C).Value2:  Next
                Else
                    V = Rg.Resize(, 6).Value2
                End If
                    oDic(Rg.Value2) = V
            End If
        Next
    Next
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 

alhagag

New Member
i no know where is the wrong changed the lebel insted of textbox but still the wrong can you attached your applied file
 

Marc L

Excel Ninja
Like when I tried Fluff13's code, I used the post #1 attachment with no mod except my VBA code only …​
 

alhagag

New Member
finally it' does work but i have show 6 column without column of date
to become like this
brandtybeoriginimportexportbalance
1200R20G580THI30010290

and show in textbox2 = 290 is it possible?
 

Marc L

Excel Ninja
As on my side … was following your previous post so that means « as on my side with post #10 » ‼​
 

Marc L

Excel Ninja
And for the third time it works on my side as you saw the video !​
All I can do now is
nothing …
 

Marc L

Excel Ninja
Like for Fluff13's code, it works on his side like on mine but not on yours …​
So the issue is on your side, something you did not well apply or something your forgot to explain​
and as we can't guess … Read again post #10, who knows ?​
 

alhagag

New Member
now it works i forgot changing properties column count was 1 but here 7 now i knew why just show one column forgive me buddy and thank alot of you i appreciate your effort
take care
 

Marc L

Excel Ninja
Ok !​
Seems weird as it works on our side (Fluff13's, me, the ones who didn't forget to like the code (thanks guys) !) without any mod …​
 
Top