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

D

djossh

Board Regular
Joined
Jul 27, 2009
Messages
243
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
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]123[/TD]
[TD]XXX[/TD]
[TD]12[/TD]
[TD]12S[/TD]
[TD]CFDS[/TD]
[TD]GRE[/TD]
[TD]FFF[/TD]
[TD]GD[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]123[/TD]
[TD]DFA[/TD]
[TD]55[/TD]
[TD]32SA[/TD]
[TD]GAS[/TD]
[TD]ADFG[/TD]
[TD]SSS[/TD]
[TD]GS[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]123[/TD]
[TD]GASD[/TD]
[TD]245[/TD]
[TD]21FSD[/TD]
[TD]AG[/TD]
[TD]DGA[/TD]
[TD]EEE[/TD]
[TD]VB[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]145[/TD]
[TD]ASDGA[/TD]
[TD]585[/TD]
[TD]1AS[/TD]
[TD]BA[/TD]
[TD]ADG[/TD]
[TD]AAA[/TD]
[TD]DF[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]145[/TD]
[TD]HFSDF[/TD]
[TD]4[/TD]
[TD]2GH[/TD]
[TD]AS[/TD]
[TD]ADG[/TD]
[TD]DDD[/TD]
[TD]GD[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]222[/TD]
[TD]SHDFE[/TD]
[TD]64[/TD]
[TD]51SD[/TD]
[TD]GR[/TD]
[TD]HSD[/TD]
[TD]JJJ[/TD]
[TD]TE[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]222[/TD]
[TD]ERRT[/TD]
[TD]33[/TD]
[TD]24FS[/TD]
[TD]SDRD[/TD]
[TD]DS[/TD]
[TD]KKK[/TD]
[TD]EW[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]222[/TD]
[TD]HFS[/TD]
[TD]24[/TD]
[TD]25S[/TD]
[TD]HRE[/TD]
[TD]SDF[/TD]
[TD]GGG[/TD]
[TD]WE[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]555[/TD]
[TD]RTHFS[/TD]
[TD]24[/TD]
[TD]64FA[/TD]
[TD]DGT[/TD]
[TD]SDFH[/TD]
[TD]RRR[/TD]
[TD]FT[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]555[/TD]
[TD]TRER[/TD]
[TD]24[/TD]
[TD]2FSAD[/TD]
[TD]SDG[/TD]
[TD]ERH[/TD]
[TD]WWW[/TD]
[TD]JH[/TD]
[/TR]
</tbody>[/TABLE]


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.

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]145[/TD]
[TD]ASDGA[/TD]
[TD]585[/TD]
[TD]1AS[/TD]
[TD]BA[/TD]
[TD]ADG[/TD]
[TD]AAA[/TD]
[TD]DF[/TD]
[/TR]
[TR]
[TD]145[/TD]
[TD]HFSDF[/TD]
[TD]4[/TD]
[TD]2GH[/TD]
[TD]AS[/TD]
[TD]ADG[/TD]
[TD]DDD[/TD]
[TD]GD[/TD]
[/TR]
</tbody>[/TABLE]

Thank you in advance.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
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.
 
Upvote 0
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.
Click to expand...

THANK YOU SO MUCH... I REALLY APPRECIATE YOUR HELP.. God Bless you...it was perfect..
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
vba auto populate texbox based on criteria
Replies
1
Views
494
AlphaFrog
AlphaFrog

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

Share this page

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