Userform Listbox header display

elsuji · Oct 5th 2020

Dear Team,

I had created the userform for Add, Update, Delete the customer details. And I am having listbox for display the details. When i am entering any values on Combobox2. it will search the customer name accordingly and display the details on Listbox1.

I want to update the header for my listbox1. The header range is Sheet "Customer Data" ("A2:H2").

My code is

Code

Sub FindAll()
'For Filtering and Updating to Listbox1 by match the ComboBox2 Value from Sheet "Custmer Data"
Dim strFind As String, rFilter As Range
With Worksheets("Customer Data")
Set rFilter = .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))
Set rng = .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))
End With
With Worksheets("Customer Data")
On Error Resume Next
If Not .AutoFilterMode Then .Range("B2").AutoFilter
On Error Resume Next
End With
strFind = Me.ComboBox2.Value
rFilter.AutoFilter Field:=2, Criteria1:="=*" & strFind & "*", Operator:=xlFilterValues
Set rng = rng.Cells.SpecialCells(xlCellTypeVisible)
Me.ComboBox2.Clear
For Each c In rng
With Me.ListBox1
.ColumnHeads = True
.ColumnCount = 8
.ColumnWidths = "30,150,100,110,80,100,75,125"
.AddItem c.Value
.List(.ListCount - 1, 1) = c.Offset(, 1)
.List(.ListCount - 1, 2) = c.Offset(, 2)
.List(.ListCount - 1, 3) = c.Offset(, 3)
.List(.ListCount - 1, 4) = c.Offset(, 4)
.List(.ListCount - 1, 5) = c.Offset(, 5)
.List(.ListCount - 1, 6) = c.Offset(, 6)
.List(.ListCount - 1, 7) = c.Offset(, 7)
.List(.ListCount - 1, 8) = c.Offset(, 8)
End With
Next c
Me.ListBox1.ListIndex = 0
With Worksheets("Customer Data")
.ShowAllData
.AutoFilterMode = False
End With
End Sub
Private Sub ComboBox2_Change()
'For search the values which is entering on ComboBox2
Dim strFind As String, FirstAddress As String, rSearch As Range
Dim f As Integer, lngItem As Long
ListBox1.Clear
With Worksheets("Customer Data")
Set rSearch = .Range(.Cells(2, 2), .Cells(.Rows.Count, 2).End(xlUp))
End With
strFind = Me.ComboBox2.Value
With rSearch
Set c = .Find(strFind)
If Not c Is Nothing Then
With Me
For lngItem = 1 To 8
' .Controls("ListBox1" & lngItem).Value = c.Offset(0, lngItem - 1).Value
Next
f = 0
End With
FirstAddress = c.Address
Do
f = f + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
If f > 0 Then
Call FindAll
End If
Else
' MsgBox "Not Found. You entered '" & strFind & "' - Please Enter a valid First Name."
End If
End With
With Worksheets("Customer Data")
If .AutoFilterMode Then
.AutoFilterMode = False
End If
End With
End Sub

Display More

I don't know how to add this headers.

I am attaching my file here for your reference.

Please help me to solve this issue

**royUK** · Oct 6th 2020

To populate a ListBox with a Range the best way is to use the List Property not AssItem with a Loop. List will not work with HeaderRows though so us Rowsource.

You cant do that in this ListBox because your range is not contiguous, you would need to Copy the cells to a temporary sheet, which isn't really worth the time.

elsuji · Oct 6th 2020

Dear Roy,

Can you please help me how to do this.

elsuji · Oct 6th 2020

Dear Roy,

I modified the code with copy paste the datas to another sheet. Now the listbox header is updating

New code is

Code

Sub FindAll()
'For Filtering and Updating to Listbox1 by match the ComboBox2 Value from Sheet "Custmer Data"
Dim strFind As String, rFilter As Range
With Worksheets("Customer Data")
Set rFilter = .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))
Set rng = .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))
End With
With Worksheets("Customer Data")
On Error Resume Next
If Not .AutoFilterMode Then .Range("B2").AutoFilter
On Error Resume Next
End With
strFind = Me.ComboBox2.Value
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Display")
Dim dsh As Worksheet
Set dsh = ThisWorkbook.Sheets("Customer Data")
rFilter.AutoFilter Field:=2, Criteria1:="=*" & strFind & "*", Operator:=xlFilterValues
Me.ComboBox2.Clear
sh.Cells.ClearContents
dsh.UsedRange.Copy
sh.Range("A1").PasteSpecial xlPasteValues
Dim Last_Row As Long
Last_Row = Application.WorksheetFunction.CountA(sh.Range("A:A"))
Set rng = rng.Cells.SpecialCells(xlCellTypeVisible)
For Each c In rng
With Me.ListBox1
.ColumnHeads = True
.ColumnCount = 8
.ColumnWidths = "30,150,100,110,80,100,75,125"
If Last_Row = 1 Then
.RowSource = "Display!A2:H2"
Else
.RowSource = "Display!A2:H" & Last_Row
End If
End With
Next c
Me.ListBox1.ListIndex = 0
With Worksheets("Display")
.ShowAllData
.AutoFilterMode = False
End With
End Sub

Display More

Code

Private Sub ComboBox2_Change()
'For search the values which is entering on ComboBox2
Dim strFind As String, FirstAddress As String, rSearch As Range
Dim f As Integer, lngItem As Long
' ListBox1.Clear
With Worksheets("Customer Data")
Set rSearch = .Range(.Cells(2, 2), .Cells(.Rows.Count, 2).End(xlUp))
End With
strFind = Me.ComboBox2.Value
With rSearch
Set c = .Find(strFind)
If Not c Is Nothing Then
With Me
For lngItem = 1 To 8
' .Controls("ListBox1" & lngItem).Value = c.Offset(0, lngItem - 1).Value
Next
f = 0
End With
FirstAddress = c.Address
Do
f = f + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
If f > 0 Then
Call FindAll
End If
Else
' MsgBox "Not Found. You entered '" & strFind & "' - Please Enter a valid First Name."
End If
End With
With Worksheets("Customer Data")
If .AutoFilterMode Then
.AutoFilterMode = False
End If
End With
End Sub

Display More

But only one small problem. When my combobox2 is blank the list box should not display any only. Only it should display value if I type any vales on Combobox2.

My request is can you please check and correct the code Please

**royUK** · Oct 6th 2020

Your code doesn't work in the example. You appear to have renamed the combobox but not edited the cide to llow for this.

elsuji · Oct 6th 2020

Dear Roy,

I really don't know. Please help me

**royUK** · Oct 6th 2020

If you change the name of a Control then you need to change any code which references it.

elsuji · Oct 6th 2020

Dear Roy,

There is no code on

Code

Private Sub ComboBox2_Change()
End Sub

**royUK** · Oct 6th 2020

Because ComboBox2 does not exist. I've moved the code to customername_Exit

elsuji · Oct 6th 2020

Dear Roy,

Thanks for your support

**royUK** · Oct 6th 2020

Pleased to help

Userform Listbox header display

Share

Similar Threads

Can you look at this code please