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

New Member
Joined
Aug 16, 2018
Messages
21
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.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
7,050
Office Version
  1. 2019
Platform
  1. Windows
Hi,
No images are showing but sharing the code behind your userform would be more helpful to forum.

Dave
 

MarkMueller

New Member
Joined
Aug 16, 2018
Messages
21
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
 

Attachments

  • Sheet.png
    Sheet.png
    12.8 KB · Views: 9
  • Search_ListBox.png
    Search_ListBox.png
    30.5 KB · Views: 9
  • Search_UserForm.png
    Search_UserForm.png
    13.4 KB · Views: 8

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
7,050
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Feb 1, 2016
Messages
3,653
Office Version
  1. 365
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,235
Messages
5,768,951
Members
425,507
Latest member
AndreaWorkPlace

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