Userform Listbox Not Displaying Correctly

zamber8

New Member
Joined
Oct 27, 2014
Messages
24
I have a Userform with 3 textboxes ("Textbox1-3") that search all active cells on sheet ("Locations") and list the found rows in the listbox ("Listbox1"). The Listbox should display 13 columns and is only used for viewing. I'm having trouble with 3 things:


  • When the results are displayed it is displaying column A from the sheet in column B on the listbox and so on leaving the first column in the listbox blank. I want the results to transfer over on the listbox the same way as the sheet displays.

  • In addition, it is searching/displaying the first row (which are the titles so shouldn't be searched)

  • Also, when I put in the count on the code below as anything over 9 I get a "Run-time error '380': Could not set the list property . Invalid property value." therefore only displaying 10 columns (first one blank and other 9 with data).

Code:
For Count = 1 To 9
ListBox1.List(ListBox1.ListCount - 1, Count) = Cells(UniqueItem(n), Count)

I've spent days looking at the code and searching the internet with no luck. I'm sure it is something very simple however I'm new at this and unable to figure it out! Thanks so much
-Amber

using Excel 2013


Code:
Public records As Variant
Option Base 1

Private Sub TextBox1_Change()
SearchText
End Sub
Private Sub TextBox2_Change()
SearchText
End Sub
Private Sub TextBox3_Change()
SearchText
End Sub


Private Sub SearchText()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("locations")
ws.Activate
Dim temp As Variant
Dim UniqueItem As Collection

Sheets(Label14.Caption).Select

temp = ThisWorkbook.ActiveSheet.UsedRange.Address
TextLen = 0
Searchbox = 1

For Count = 1 To 3
If Len(Me.Controls("Textbox" & Count).Value) > TextLen Then
TextLen = Len(Me.Controls("Textbox" & Count).Value)
strValueToPick = Me.Controls("Textbox" & Count).Value
End If
Next

If TextLen < 1 Then Exit Sub

On Error Resume Next

    With Range(ThisWorkbook.ActiveSheet.UsedRange.Address)
        Set rngfind = .Find(strValueToPick, .Cells(1, 1), LookIn:=xlFormulas, Lookat:=xlPart)
        If Not rngfind Is Nothing Then
            strFirstAddress = rngfind.Address
            Set rngPicked = rngfind
            Do
                Set rngPicked = Union(rngPicked, rngfind)
                Set rngfind = .FindNext(rngfind)
            Loop While Not rngfind Is Nothing And rngfind.Address <> strFirstAddress
        End If
    End With
    
    If strFirstAddress = "" Then Exit Sub
    If Not rngPicked Is Nothing Then
        rngPicked.Select
    End If

ListBox1.Clear

Set UniqueItem = New Collection
    
For Each c In Selection

RowText = Join(Application.Transpose(Application.Transpose(Range(Cells(c.Row, 1), Cells(c.Row, 13)).Value)), " ")
If Len(TextBox1.Text) > 0 And InStr(LCase(RowText), Trim(LCase(TextBox1.Text))) = 0 Then GoTo 20
If Len(TextBox2.Text) > 0 And InStr(LCase(RowText), Trim(LCase(TextBox2.Text))) = 0 Then GoTo 20
If Len(TextBox3.Text) > 0 And InStr(LCase(RowText), Trim(LCase(TextBox3.Text))) = 0 Then GoTo 20

On Error Resume Next

UniqueItem.Add CStr(c.Row), CStr(c.Row)

On Error GoTo 0

20 Next c

For n = 1 To UniqueItem.Count
    ListBox1.AddItem
UniqueItem (n)


For Count = 1 To 9
   ListBox1.List(ListBox1.ListCount - 1, Count) = Cells(UniqueItem(n), Count)
Next
Next

End Sub
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Leith Ross

Well-known Member
Joined
Mar 17, 2008
Messages
1,874
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
Hello zamber8,

The ListBox is a zero based array where the first element is at index zero. You have place the Option Base 1 statement in your module which forces all arrays to start at index one. This why you see the data shifted in the ListBox.

If you remove the Option Base 1 statement, the code should work. I advise against using this statement because of the problems it cause. If you need to an array to start at one then explicit declare the array dimemsions like Data(1 to 10).
 

zamber8

New Member
Joined
Oct 27, 2014
Messages
24
Hi Leith,

Thanks for responding! I've removed the Option Base 1 statement and the listbox is still putting the data in the second column instead of starting in the first also how can I fix it to not search the first row and to allow the count to be greater than 9 in the For count code (currently gives a Runtime Error for anything over 9). Any other ideas on how I can fix these issues? Thanks again for your help

Code:
For Count = 1 To 9
ListBox1.List(ListBox1.ListCount - 1, Count) = Cells(UniqueItem(n), Count)
 

Leith Ross

Well-known Member
Joined
Mar 17, 2008
Messages
1,874
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
Hello zamber8,

The ListBox defaults to 10 columns (0 - 10). To expand this you must either assign a Range to the ListBox or create a 2-D Variant array to assign to it.

It would help if I had access to copy of your workbook. Can you upload a copy to file sharing service?
 

Leith Ross

Well-known Member
Joined
Mar 17, 2008
Messages
1,874
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
Hello zamber8,

Thanks for posting the workbook. After reviewing your code and making some changes, I realized I am not sure how your search operation is supposed to work. Can you explain how it is supposed to work?
 

zamber8

New Member
Joined
Oct 27, 2014
Messages
24

ADVERTISEMENT

Hi Leith

This userform is only for viewing and/or searching the contents of one of the sheets in the workbook. once I'm finished with all the coding the user will only be able to view/add/edit data through userforms and not see the actual sheets through excel).

When this userform is opened the sheet ("Locations") data is displayed in listbox1. The total number of rows will always be changing however the columns to be displayed (13) should always stay the same. There are three textboxes that one or all can be used to narrow/search the contents of the sheet and then replace the listbox contents (that displays the full sheet) with the rows that match the search criteria in any of the 3 textboxes (each textbox searches all the active rows/columns not just one specific column).

For example the user could use the first textbox to search by state then use the second textbox to narrow those results down to all locations in that particular state with a certain district manager's name and then the third textbox to further narrow the results.

The user can click on any of the rows in the listbox which will then display that rows contents in label captions above the listbox (this is just to separate and view the selected row easier from the listbox full of data). When the search text boxes are cleared of the text that was searched for the original full view of all the data in the sheet returns to the listbox.

The workbook and userform that I submitted was a simplified version I've been using for testing perhaps the original will help show the function of the userform better (see below). I'm new at this and have tried to figure the three issues I'm having out for days as to not bother anyone but have had no luck. Keeping my fingers crossed there is an answer!

Thanks again for your help

ListboxTest.xlsm
 

Leith Ross

Well-known Member
Joined
Mar 17, 2008
Messages
1,874
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
Hello zamber8,

Thanks for the followup information. Try the attached workbook and let me know if it needs anything.

ListBox Test ver2.xlsm
 

zamber8

New Member
Joined
Oct 27, 2014
Messages
24
Leith

Thank you, Thank you, Thank you! This is great. I've put your code on the original form that has all the other controls and it works great. Did have a couple questions.

On the listbox display it is skipping over column B and D. Originally these did not have text in row 1 and the text started in row 2. I added the titles to the top of those two column's thinking it was hiding them due to them being blank however even after adding the text to B1 and D1 it still is hiding. Any ideas?

Also, I know I didn't ask this originally however it would be nice if there was a way to disable to top "header" row from being selected/clicked in the Listbox. When the user clicks the rows in the listbox it brings the data up to label captions to separate from the rest of the list for easier viewing. Currently when the top title row is clicked it shows the titles in the captions which I would prefer it not. I tried creating an If statement in the Listbox Click however it didn't work. Is there a way around this?

Again I can't thank you enough for your help!

Amber

ListboxTestVER3

Code:
Private Sub ListBox1_Click()

iRow = ListBox1.ListIndex

Me.Label58.Caption = ListBox1.List(iRow, 0)

If Me.Label58.Caption <> "Center Type" Then
  
If Len(TextBox1.Text) = 0 And Len(TextBox2.Text) = 0 And Len(TextBox3.Text) = 0 Then
Me.Label26.Caption = ListBox1.List(iRow, 0) & " - " & ListBox1.List(iRow, 1)
Me.Label40.Caption = ListBox1.List(iRow, 2) & " - " & ListBox1.List(iRow, 3)
Me.Label42.Caption = ListBox1.List(iRow, 4)
Me.Label43.Caption = ListBox1.List(iRow, 5) & "   " & ListBox1.List(iRow, 6) & ", " & ListBox1.List(iRow, 7) & " " & ListBox1.List(iRow, 8)
Me.Label36.Caption = ListBox1.List(iRow, 6)
Me.Label19.Caption = ListBox1.List(iRow, 9)
Me.Label41.Caption = ListBox1.List(iRow, 10) & "  " & ListBox1.List(iRow, 11)
End If

If Len(TextBox1.Text) > 0 Or Len(TextBox2.Text) > 0 Or Len(TextBox3.Text) > 0 Then
Me.Label26.Caption = ListBox1.List(iRow, 0) & " - " & ListBox1.List(iRow, 1)
Me.Label40.Caption = ListBox1.List(iRow, 2) & " - " & ListBox1.List(iRow, 3)
Me.Label42.Caption = ListBox1.List(iRow, 4)
Me.Label43.Caption = ListBox1.List(iRow, 5) & "   " & ListBox1.List(iRow, 6) & ", " & ListBox1.List(iRow, 7) & " " & ListBox1.List(iRow, 8)
Me.Label36.Caption = ListBox1.List(iRow, 6)
Me.Label19.Caption = ListBox1.List(iRow, 9)
Me.Label41.Caption = ListBox1.List(iRow, 10) & "  " & ListBox1.List(iRow, 11)
End If 

End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,093
Members
414,501
Latest member
mdhaumyu

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
Top