Excel VBA, using a User Form to Search up to 4 columns and show the results in a List Box, as part of the form.

[MarkMueller]

fIt's good to have this site to bounce questions off experts. So this is my first attempt at using MrExcel, or any other site, to assist with problem resolution or to present a better way of coding.
So for my request; I am helping my wife with a workbook she needs for work. I have created a UserForm that incorporates 4 search (labels), but I have only been able to search for one at a time. The key is to be able to search for any number of the Search labels and in essence create a drill-down effect, with the data being displayed in the Listbox.

I have attached some images of the Form and the basic code. I am sure there are better ways to do it, but I guess I am limited in knowledge. So you may look at the coding and laugh a little which is absolutely fine. I am old school (DOS, QBASIC, Etc) and there is certainly a lot to grasp within Excel VBA. But I am working on it.

So just to illustrate the need more clearly: UserForm with 4 Labels (Used to Search the Master Worksheet), currently a single search with results being displayed in the Listbox. I can search without a problem for each of the Four (Labels) and they return the results into the Listbox, the results can be copied into an existing workbook as a named worksheet, and then a table is created that is used in a separate function (An additional Macro) for drilling down. It has become necessary to utilize any of the Search(s) (Labels = "Vendor", "Version", ")Author", "Language") that are column-oriented to establish a different view of the data.

I hope this makes sense when you review the information provided and I appreciate anyone's assistance. This is just a great learning experience, thank you.

 

[dmt32]

Hi,
No images are showing but sharing the code behind your userform would be more helpful to forum.

Dave

 

[MarkMueller]

Sorry forgot to load images. I noticed that there is no option to load a text file (Code). So I am going to do it here?!

Private Sub ClearForm(Except As String)

Worksheets("Master List").Activate
' ---------- Not all Case's are listed, only selected 2 search items -------------

Select Case Except

Case "REF"
FormEvents = False
Version.Value = ""
Author.Value = ""
Language.Value = ""
' Results.Clear
FormEvents = True

Case "Product Version"
FormEvents = False
REF.Value = ""
Author.Value = ""
Language.Value = ""
'Results.Clear
FormEvents = True
End Select

End Sub
Private Sub cmdAdd_Click()
' The Worksheet being created is "Research", make sure all sheet references are named that!
'-------------------------------------------------------------------------------------------------------------------
Dim wb As Workbook
Dim Headers() As Variant
Dim ws As Worksheet
Set wb = ActiveWorkbook '<<<< After export data, return to this original wb.
' -------------- See if Worksheet "Research" Exists, if it does delete it.
Dim xWorksheet As Worksheet
For Each xWorksheet In ActiveWorkbook.Worksheets
If xWorksheet.Name = "Research" Then
Application.DisplayAlerts = False
xWorksheet.Delete
Application.DisplayAlerts = True
End If
Next xWorksheet
' -------------------------------------------------
With Workbooks.Add
.Sheets(1).Range(Cells(2, 1), Cells(Me.Results.ListCount, Me.Results.ColumnCount)) = Me.Results.List
Sheets(1).Name = "Research"
End With

' ------------- Build worksheet "Research" with data
ActiveSheet.Move Before:=Workbooks("Chg-WORKING-Material-Publication-Catalog_v2").Sheets(1)
Range("A1:L1").Value = Array("Record", "Material Count", "Name", "Parent Course", "Product Version", "Customer Specific", "Drive Side", "Mission Type", "Material Type", "Author", "Language")
Sheets(1).ListObjects.Add(xlSrcRange, Range("A1:K495"), , xlYes).Name = "Research"
' --------------- Format the Workbook.
Range("D:D").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = True
.ColumnWidth = 25
End With
Range("E:E").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.ColumnWidth = 14
End With
Range("E2:K50").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Rows("1:1").Select
Selection.RowHeight = 28
Columns("C:C").ColumnWidth = 45
End Sub

Private Sub SearchBtn_Click()

Dim SearchTerm As String
Dim SearchColumn As String
Dim RecordRange As Range
Dim FirstAddress As String
Dim FirstCell As Range
Dim RowCount As Integer
Dim addme As Range
Dim x As Long

' Display an error if no search term is entered
If REF.Value = "" And Version.Value = "" And Author.Value = "" And Language.Value = "" Then

MsgBox "No search term specified", vbCritical + vbOKOnly
Exit Sub

End If

' Work out what is being searched for
If REF.Value <> "" Then

SearchTerm = REF.Value
SearchColumn = "REF"

End If

If Version.Value <> "" Then

SearchTerm = Version.Value
SearchColumn = "Product Version"

End If

If Author.Value <> "" Then

SearchTerm = Author.Value
SearchColumn = "Author"

End If

If Language.Value <> "" Then

SearchTerm = Language.Value
SearchColumn = "Language"

End If

' Results.Clear

' Only search in the relevant table column i.e. if somone is searching Author
' only search in the Author column
With Range("Catalog[" & SearchColumn & "]")

' Find the first match
Set RecordRange = .Find(SearchTerm, LookIn:=xlValues)

' If a match has been found
If Not RecordRange Is Nothing Then

FirstAddress = RecordRange.Address
RowCount = 0

Do

' Set the first cell in the row of the matching value
Set FirstCell = Range("A" & RecordRange.Row)

' Add matching record to List Box
Results.AddItem
Results.List(RowCount, 0) = FirstCell(1, 1)
Results.List(RowCount, 1) = FirstCell(1, 2)
Results.List(RowCount, 2) = FirstCell(1, 3)
Results.List(RowCount, 3) = FirstCell(1, 4)
Results.List(RowCount, 4) = FirstCell(1, 5)
Results.List(RowCount, 5) = FirstCell(1, 6)
Results.List(RowCount, 6) = FirstCell(1, 7)
Results.List(RowCount, 7) = FirstCell(1, 8)
Results.List(RowCount, 8) = FirstCell(1, 9)
Results.List(RowCount, 9) = FirstCell(1, 10)
RowCount = RowCount + 1

' Look for next match
Set RecordRange = .FindNext(RecordRange)

' When no further matches are found, exit the sub
If RecordRange Is Nothing Then

Exit Sub

End If

' Keep looking while unique matches are found
Loop While RecordRange.Address <> FirstAddress

Else

' If you get here, no matches were found
Results.AddItem
Results.List(RowCount, 0) = "Nothing Found - Try New Search"

End If

End With

End Sub

 

[dmt32]

When posting code look at the menu bar & press VBA - this creates codes tags for you to place code
another posting option would be to place copy of your file on file sharing site like dropbox & provide a link to it

Dave

 

[Akuini]

Question:
1. Do you want to search by just a single keyword at a time? for example if you type in Author then other textboxes value aren't relevant to the search.
2. Which one is "Reference" column?
3. Could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive?
And then share the link here.