Displaying selection from Listbox on userform

vikrampnz · Jul 21st 2006

Hi

I have been trying to create a "Search" or "Look Up" form for my database. (Attached file - "Test - Form").

I have been given a lot of help/ideas from this forum with which I managed to get to the stage where I could select the criteria i wanted to search by using a combobox and textbox in the userform. On hitting the "Find" button it shows all the results in the listbox.

The trouble started when I tried to display the listbox selection on the labels at the bottom of the userform. As the listbox is small and cant show all the fields properly, I need to display them in labels once user selects a particular record from listbox.

I managed to find some examples of this from this forum. (file attached "Action Log"). As I am not an Excel/ VBA expert, I have missed something and am not able to make it work.

Can someone please look at my file "Test - Search Form" and tell me where I have gone wrong. It keeps giving me message saying "Out of range". I am not sure how to define ranges correctly.

I would appreciate any help!

cheers

fengore · Jul 21st 2006

Re: Displaying selection from Listbox on userform

without looking at your files, the code for this is usually something like

Code

textbox1 = listbox1.list(listbox1.listindex, 0)

the final ,0 is not needed if you are only using one column.

But if you are using say 4 columns,

Code

listbox1.columncount = 4

then you can specify the item with

Code

textbox1 = listbox1.list(listbox1.listindex, 0)
textbox2 = listbox1.list(listbox1.listindex, 1)
textbox3 = listbox1.list(listbox1.listindex, 2)
textbox4 = listbox1.list(listbox1.listindex, 3)

vikrampnz · Jul 21st 2006

Re: Displaying selection from Listbox on userform

hi fengore....thanks for your response. I have tried to use following code, but it is giving me error saying : Run time error "9" , "Subscript out of range".

Here's what I've got:

Code

Private Sub cmbSelect_Click()
    Dim r As Integer
    r = Me.ListBox1.ListIndex
    
    If Me.ListBox1.ListIndex < 1 Then    'not selected
        MsgBox " No selection made"
    'ElseIf Me.ListBox1.ListIndex = r Then
        'MsgBox " No selection made"
    Application.DisplayAlerts = True
    
    ElseIf Me.ListBox1.ListIndex >= 1 Then  'User has selected
        r = Me.ListBox1.ListIndex
        Me.ListBox1.ColumnCount = 4
        
        With Me
            .compno.Caption = ListBox1.List(r, 0)
            .lbldate.Caption = ListBox1.List(r, 1)
            .customer.Caption = ListBox1.List(r, 2)
            .conper.Caption = ListBox1.List(r, 3)
            
        End With
    End If
    
End Sub

Display More

I have feeling that I am not specifying the range properly. following is the complete code in the userform:

Please note:
The worksheet in which the data is stored is called "ComplaintData" and it will be hidden (will not be the active sheet).

Code

Option Explicit
Dim MyArray(65536, 2)
Public MyData As Range, a As Range, b As Range, c As Range, d As Range, e As Range, f As Range, g As Range, h As Range, i As Range


Private Sub cmdPrint_Click()
Dim iListCount As Integer, iColCount As Integer
Dim iRow As Integer
Dim rStartCell As Range


'Set a range variable to the first cell to recieve our data
'Using "End(xlUp).Offset(1, 0)" _
 will give us the cell below the last entry
Set rStartCell = Sheet2.Range("A65536").End(xlUp).Offset(1, 0)


  'Loop as many times (less one) as there are entries in our list.
  'We must start from zero to use this in the Selected Property.
  For iListCount = 0 To ListBox1.ListCount - 1
   If ListBox1.Selected(iListCount) = True Then  'User has selected
      ListBox1.Selected(iListCount) = False
      iRow = iRow + 1
      'Now loop as many times as there are columns in MyRange
      For iColCount = 0 To Range("MyRange").Columns.Count - 1
      'place the selected data into the table, starting from _
       range Ax and moving across as many columns as there are _
       in the range MyRange.
       rStartCell.Cells(iRow, iColCount + 1).Value = _
       ListBox1.List(iListCount, iColCount)
      Next iColCount
   End If
Next iListCount


Set rStartCell = Nothing


End Sub


Private Sub ComboBox1_Enter()
 ListBox1.RowSource = ""
End Sub


Private Sub cmdClose_Click()
  Unload Me
End Sub


Private Sub cmbFind_Click()
  Set c = Range("a65536").End(xlUp).Offset(1, 0)
        'write userform entries to database
    Dim DataSH As Worksheet
  Set DataSH = Sheets("ComplaintData")
  DataSH.Range("L1").Value = ComboBox1.Value
  DataSH.Range("L2").Value = TextBox1.Text
  DataSH.Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, criteriarange:=DataSH.Range("L1:L2"), copytorange:=DataSH.Range("N1:U1")
  ListBox1.RowSource = "ComplaintData!" & DataSH.Range("outdata").Address
End Sub


Private Sub UserForm_Initialize()
ComboBox1.List = WorksheetFunction.Transpose(Sheets("complaintdata").Range("A1:I1"))
Set MyData = Sheets("ComplaintData").Range("a2").CurrentRegion   'database
    
   End Sub
   


Private Sub cmbSelect_Click()
    Dim r As Integer
    r = Me.ListBox1.ListIndex
    
    If Me.ListBox1.ListIndex < 1 Then    'not selected
        MsgBox " No selection made"
    'ElseIf Me.ListBox1.ListIndex = r Then
        'MsgBox " No selection made"
    Application.DisplayAlerts = True
    
    ElseIf Me.ListBox1.ListIndex >= 1 Then  'User has selected
        r = Me.ListBox1.ListIndex
        Me.ListBox1.ColumnCount = 4
        
        With Me
            .compno.Caption = ListBox1.List(r, 0)
            .lbldate.Caption = ListBox1.List(r, 1)
            .customer.Caption = ListBox1.List(r, 2)
            .conper.Caption = ListBox1.List(r, 3)
            
        End With
    End If
    
End Sub

Display More

Your help will be much appreciated!
Cheers

Dave Hawley · Jul 21st 2006

Re: Displaying selection from Listbox on userform

Which line causes the error?

vikrampnz · Jul 23rd 2006

Re: Displaying selection from Listbox on userform

Hi Dave....sorry for late reply over the weekend I didnt have access to my office comp....

It has started doing funny things now.....now it has started giving me error
"1004 - Extract name has a missing or illegal field name" in the following line:

Code

DataSH.Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, criteriarange:=DataSH.Range("L1:L2"), copytorange:=DataSH.Range("N1:U1")

This part was working fine before....but i think i managed to mess up with the range while trying to get the other part to work....

cheers

Dave Hawley · Jul 24th 2006

Re: Displaying selection from Listbox on userform

You probably need to Clear the range before having the filtered dat copied there. That is

Code

With DataSH
     .Range("N1").CurrentRegion.Clear
     .Range("A1").CurrentRegion.AdvancedFilter _
      Action:=xlFilterCopy, criteriarange:=.Range("L1:L2"), _
      copytorange:=.Range("N1")
 End With

vikrampnz · Jul 24th 2006

Re: Displaying selection from Listbox on userform

Hi Dave, I managed to solve problem in my original file where I can get the the userform1 to search & display record lines in the listbox again.

Now the problem I have is:

1. If I try to search by a customer thats not in the database (in sheet "ComplaintData", then it gives me error. I would like it to say something like "There no complaint for this Cusdtomer"

2. When I search by customer that is in the databse, it shows results in the listbox on userform1. As soon as I click on the line in the listbox, it gives me an error. I want to be able to select record in the listbox, as eventually theer will be more than one records for each customer e.g. complaint for same customer but different product or category)

3. I want to be able to click on the desired line in list box and display the details of that record in the labels below, as the list box doesnot have enough space to show all the fields completely.

Hope you can help....cheers

vikrampnz · Jul 24th 2006

Re: Displaying selection from Listbox on userform

sorry I had a password on it...i have removed it now..! Cheers

vikrampnz · Jul 24th 2006

Re: Displaying selection from Listbox on userform

Hi...I managed to make it work....I used following code:

Code

Private Sub Listbox1_Click()
  Dim rng As Range
  Set rng = Worksheets("ComplaintData").Range("A1:J65536")
  If ListBox1.ListIndex <> -1 Then
     compno.Caption = Application.VLookup(ListBox1.Value, rng, 1, False)
     lbldate.Caption = Application.VLookup(ListBox1.Value, rng, 2, False)
     customer.Caption = Application.VLookup(ListBox1.Value, rng, 3, False)
     conper.Caption = Application.VLookup(ListBox1.Value, rng, 4, False)
     product.Caption = Application.VLookup(ListBox1.Value, rng, 5, False)
     batch.Caption = Application.VLookup(ListBox1.Value, rng, 6, False)
     Category.Caption = Application.VLookup(ListBox1.Value, rng, 7, False)
     am.Caption = Application.VLookup(ListBox1.Value, rng, 9, False)
     lbldesc.Caption = Application.VLookup(ListBox1.Value, rng, 8, False)
     End If
End Sub

Display More

Thanks:)

Dave Hawley · Jul 24th 2006

Re: Displaying selection from Listbox on userform

Thanks for sharing vikrampnz.

epynephrin · May 8th 2007

Re: Displaying selection from Listbox on userform

I know this is an old thread, but I wanted to thank you, Vikrampnz. You happened to solve one of my problems on a similar note, so I'm in a good mood now.

Danke!

manilara · Jan 9th 2013

Re: Displaying selection from Listbox on userform

I coultnt open the Properties window Its has been protected with password can u share password

**cytop** · Jan 9th 2013

Re: Displaying selection from Listbox on userform

This thread is 6 years old and vikrampnz has not been active on the board for some time. Not much chance of getting the password.

Displaying selection from Listbox on userform

Participate now!

Share