VBA Populate Listbox / Edit Listbox based on Criteria on my Textbox1

djossh · Dec 12, 2018

I have data in sheet4 (contains 8 columns), I need to populate my list box using criteria from my textbox1
After populating my listbox I will select the cell or data that I need to edit (From the Listbox) and save it on my worksheet.

SAMPLE DATA

	A	B	C	D	E	F	G	H
1	123	XXX	12	12S	CFDS	GRE	FFF	GD
2	123	DFA	55	32SA	GAS	ADFG	SSS	GS
3	123	GASD	245	21FSD	AG	DGA	EEE	VB
4	145	ASDGA	585	1AS	BA	ADG	AAA	DF
5	145	HFSDF	4	2GH	AS	ADG	DDD	GD
6	222	SHDFE	64	51SD	GR	HSD	JJJ	TE
7	222	ERRT	33	24FS	SDRD	DS	KKK	EW
8	222	HFS	24	25S	HRE	SDF	GGG	WE
9	555	RTHFS	24	64FA	DGT	SDFH	RRR	FT
11	555	TRER	24	2FSAD	SDG	ERH	WWW	JH

SAMPLE LISTBOX below, based on my criteria from my textbox1 = 145 (column A)
here if i will edit something (except column A) I want the changes to be save/replace the previous data.

145	ASDGA	585	1AS	BA	ADG	AAA	DF
145	HFSDF	4	2GH	AS	ADG	DDD	GD

Thank you in advance.

RSpin · Dec 15, 2018

I'm assuming you're some what familiar with userforms, considering you have a textbox and listbox already.

To get listbox1 to populate based on testbox1:

Code:

Private Sub TextBox1_Change()


Dim TBL() As Variant
Dim BB As Integer
Dim CC As Integer
Dim DD As Integer


TBL = Sheet4.Range("A1:H11")
UserForm1.ListBox1.Clear
DD = 0
For BB = 1 To UBound(TBL, 1)
    If Val(TextBox1.Value) = Val(TBL(BB, 1)) Then
        DD = DD + 1
        With UserForm1.ListBox1
            .ColumnCount = 8
            .AddItem
            For CC = 1 To 8
               .Column(CC - 1, DD - 1) = TBL(BB, CC)
            Next CC
        End With
    End If
Next BB


End Sub

The only way I've found to be able to edit the data in the listbox, is to create 7 more textboxes (TextBox2 throught Textbox 8) and a commandbutton (CommandButton1). When the item is selected in the ListBox, the rest of the textboxes are populated with the correct information. Then once you have changed whatever it is that you want to change, click the commandbutton and everything will update with what you want. I set the visibility of each of these new textboxes and the commandbutton to False, so they only show up when you select an item from the list box. Here is the code:

Code:

Private Sub CommandButton1_Click()


Dim TBL() As Variant
Dim BB As Integer
Dim CC As Integer
Dim DD As Integer
Dim EE As Integer


TBL = Sheet4.Range("A1:H11")
EE = 0


For BB = 0 To UserForm1.ListBox1.ListCount - 1
    If UserForm1.ListBox1.Selected(BB) Then
        For CC = 1 To UBound(TBL, 1)
            For DD = 1 To 8
                If Val(UserForm1.ListBox1.List(BB, DD - 1)) <> Val(TBL(CC, DD)) Then
                    Exit For
                Else
                    If DD = 8 Then
                        EE = 1
                    End If
                End If
            Next DD
            If EE = 1 Then
                Exit For
            End If
        Next CC
        Exit For
    End If
Next BB


If EE = 1 Then
    For DD = 2 To 8
        TBL(CC, DD) = UserForm1.Controls("TextBox" & DD).Value
        UserForm1.Controls("TextBox" & DD).Visible = False
    Next DD
    UserForm1.CommandButton1.Visible = False
    Sheet4.Range("A1:H11") = TBL
End If


TBL = Sheet4.Range("A1:H11")
UserForm1.ListBox1.Clear
DD = 0
For BB = 1 To UBound(TBL, 1)
    If Val(TextBox1.Value) = Val(TBL(BB, 1)) Then
        DD = DD + 1
        With UserForm1.ListBox1
            .ColumnCount = 8
            .AddItem
            For CC = 1 To 8
               .Column(CC - 1, DD - 1) = TBL(BB, CC)
            Next CC
        End With
    End If
Next BB
           
End Sub


Private Sub ListBox1_Click()


Dim BB As Integer
Dim CC As Integer


For BB = 0 To UserForm1.ListBox1.ListCount - 1
        If UserForm1.ListBox1.Selected(BB) Then
            For CC = 1 To UserForm1.ListBox1.ColumnCount
                UserForm1.Controls("TextBox" & CC).Visible = True
                UserForm1.Controls("TextBox" & CC).Value = UserForm1.ListBox1.List(BB, CC - 1)
            Next CC
            UserForm1.CommandButton1.Visible = True
        End If
Next BB


End Sub

I hope this is what you're looking for, or at least helps out a bit.

djossh · Jan 6, 2019

RSpin said:

I'm assuming you're some what familiar with userforms, considering you have a textbox and listbox already.

To get listbox1 to populate based on testbox1:

Code:

Private Sub TextBox1_Change()


Dim TBL() As Variant
Dim BB As Integer
Dim CC As Integer
Dim DD As Integer


TBL = Sheet4.Range("A1:H11")
UserForm1.ListBox1.Clear
DD = 0
For BB = 1 To UBound(TBL, 1)
    If Val(TextBox1.Value) = Val(TBL(BB, 1)) Then
        DD = DD + 1
        With UserForm1.ListBox1
            .ColumnCount = 8
            .AddItem
            For CC = 1 To 8
               .Column(CC - 1, DD - 1) = TBL(BB, CC)
            Next CC
        End With
    End If
Next BB


End Sub

The only way I've found to be able to edit the data in the listbox, is to create 7 more textboxes (TextBox2 throught Textbox 8) and a commandbutton (CommandButton1). When the item is selected in the ListBox, the rest of the textboxes are populated with the correct information. Then once you have changed whatever it is that you want to change, click the commandbutton and everything will update with what you want. I set the visibility of each of these new textboxes and the commandbutton to False, so they only show up when you select an item from the list box. Here is the code:

Code:

Private Sub CommandButton1_Click()


Dim TBL() As Variant
Dim BB As Integer
Dim CC As Integer
Dim DD As Integer
Dim EE As Integer


TBL = Sheet4.Range("A1:H11")
EE = 0


For BB = 0 To UserForm1.ListBox1.ListCount - 1
    If UserForm1.ListBox1.Selected(BB) Then
        For CC = 1 To UBound(TBL, 1)
            For DD = 1 To 8
                If Val(UserForm1.ListBox1.List(BB, DD - 1)) <> Val(TBL(CC, DD)) Then
                    Exit For
                Else
                    If DD = 8 Then
                        EE = 1
                    End If
                End If
            Next DD
            If EE = 1 Then
                Exit For
            End If
        Next CC
        Exit For
    End If
Next BB


If EE = 1 Then
    For DD = 2 To 8
        TBL(CC, DD) = UserForm1.Controls("TextBox" & DD).Value
        UserForm1.Controls("TextBox" & DD).Visible = False
    Next DD
    UserForm1.CommandButton1.Visible = False
    Sheet4.Range("A1:H11") = TBL
End If


TBL = Sheet4.Range("A1:H11")
UserForm1.ListBox1.Clear
DD = 0
For BB = 1 To UBound(TBL, 1)
    If Val(TextBox1.Value) = Val(TBL(BB, 1)) Then
        DD = DD + 1
        With UserForm1.ListBox1
            .ColumnCount = 8
            .AddItem
            For CC = 1 To 8
               .Column(CC - 1, DD - 1) = TBL(BB, CC)
            Next CC
        End With
    End If
Next BB
           
End Sub


Private Sub ListBox1_Click()


Dim BB As Integer
Dim CC As Integer


For BB = 0 To UserForm1.ListBox1.ListCount - 1
        If UserForm1.ListBox1.Selected(BB) Then
            For CC = 1 To UserForm1.ListBox1.ColumnCount
                UserForm1.Controls("TextBox" & CC).Visible = True
                UserForm1.Controls("TextBox" & CC).Value = UserForm1.ListBox1.List(BB, CC - 1)
            Next CC
            UserForm1.CommandButton1.Visible = True
        End If
Next BB


End Sub

I hope this is what you're looking for, or at least helps out a bit.

THANK YOU SO MUCH... I REALLY APPRECIATE YOUR HELP.. God Bless you...it was perfect..

VBA Populate Listbox / Edit Listbox based on Criteria on my Textbox1

djossh

Board Regular

Excel Facts

RSpin

New Member

djossh

Board Regular

Similar threads

Forum statistics

Share this page

VBA Populate Listbox / Edit Listbox based on Criteria on my Textbox1

djossh

Board Regular

Excel Facts

RSpin

New Member

djossh

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock