Expert: Tom Ogilvy - 10/12/2008

Question
Hi

Sorry but first I thought of the radio button as an idea, but truly the point is that the worksheet should be searched based on the user input, but it has to be displayed in three different place on the user form(three rows with column headings ).This is because there could be more than one relevant row,and in Column K would be the data to further filter the results.
This would be Approved, Unapproved, Superseded.
First it should search the Column A based on the user input(number), the cells in this column contains the data which looks like this: SSL0023. When the user enters 23 in the input box it should display the relevant rows on the User Form.(it can be more-no more than 10)
Basically there should be 1 inputbox, 1 command button,after clicking on the button it would search the worksheet column A and display all the relevant rows in Listbox, Text box, or Excel Grid.(whichever is better)
Inthe Worksheet there are 15 Columns, and 170 Rows(this will be more and more)
The column cells contain mainly numbers(short data), except 1 column where the particular item is described in 1-2 sentences.
Hopefully this helps

Many Thanks



Last query:
on the user form the user would enter a number into a textfield and select one radio button.This would be the search criteria in the worksheet. After clicking on the button it would display the relevant rows in the User Form.


Answer
Zsoft,


Not sure what you mean here.  Do you mean you want three listboxes or you want to make a selection between approved, unapproved, superseded.

Anyway, a listbox can only show 9 columns unless it is bound to a worksheet, so I have chosen to filter your data, then copy it to a sheet named Dummy (which is added if it doesn't exist) and put the subset there - it then binds the listbox to that range.  Also, you can not use the columnheads property unless the rowsource is bound to a worksheet - another reason to use the dummy sheet.  

I think trying to show a 1 or two sentence description in a listbox would be a non-starter.   Maybe you just need to have the userform apply a filter to the worksheet and look at the data there. Anyway, here is the code:


Private Sub CommandButton1_Click()
Dim r1 As Range, r2 As Range, r2Header As Range
Dim sh As Worksheet, r As Range
On Error Resume Next
Set sh = Worksheets("Dummy")
If Not sh Is Nothing Then
  sh.Cells.ClearContents
Else
  Set sh = Worksheets.Add(After:=Worksheets(Worksheets.Count))
  sh.Name = "Dummy"
End If
On Error GoTo 0
With Worksheets("Data")
  Set r = .Range("A1").CurrentRegion
  r.AutoFilter Field:=1, _
    Criteria1:="=*" & Trim(TextBox1.Text)
  r.AutoFilter
End With

Set r1 = r.Columns(1).SpecialCells(xlVisible)
Set r2 = Intersect(r1.EntireRow, r)
Debug.Print r.Address, r1.Address, r2.Address
r2.Select

r2.Copy sh.Range("A1")
Set r2 = sh.Range("A1").CurrentRegion
Set r2Header = r2.Rows(1)
Set r2 = r2.Offset(1, 0).Resize(r2.Rows.Count - 1)
ListBox1.ColumnCount = 15
ListBox1.RowSource = ""
ListBox1.RowSource = r2.Address(1, 1, xlA1, True)
ListBox1.ColumnHeads = True
End Sub

--
Regards,
Tom Ogilvy