• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Show full row in userform search|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / Show full row in userform search|VBA & Macros|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosShow full row in userform search
sp_PrintTopic sp_TopicIcon
Show full row in userform search
Avatar
Stuart Allison

New Member
Members
Level 0
Forum Posts: 2
Member Since:
March 4, 2021
sp_UserOfflineSmall Offline
1
March 4, 2021 - 9:42 pm
sp_Permalink sp_Print

I used the code in the tutorial below to make a search box for my table. Everything runs fine except the results. Rather than showing the full row, it is only showing the result in column A. I made sure the names of columns, search box, text box etc. all match so not too sure where i'm going wrong. 

 

In the downloaded spreadsheet it shows columns A - D, i need it to show A - G and from what i can tell it should be working. Any help would be appreciated. 

 

https://www.myonlinetraininghu.....rm-listbox - link to tutorial 

My code for search button:

 

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

' Display an error if no search term is entered
If Machine.Value = "" And SerialNumber.Value = "" And Location.Value = "" And Company.Value = "" And ContactNumber.Value = "" And PersonToContact.Value = "" And Email.Value = "" Then

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

End If

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

SearchTerm = Machine.Value
SearchColumn = "Machine"

End If

If SerialNumber.Value <> "" Then

SearchTerm = SerialNumber.Value
SearchColumn = "Serial Number"

End If

If Location.Value <> "" Then

SearchTerm = Location.Value
SearchColumn = "Location"

End If

If Company.Value <> "" Then

SearchTerm = Company.Value
SearchColumn = "Company"

End If

If ContactNumber.Value <> "" Then

SearchTerm = ContactNumber.Value
SearchColumn = "Contact Number"

End If

If PersonToContact.Value <> "" Then

SearchTerm = PersonToContact.Value
SearchColumn = "Person To Contact"

End If

If Email.Value <> "" Then

SearchTerm = Email.Value
SearchColumn = "Email"

End If
Results.Clear

' Only search in the relevant table column i.e. if somone is searching Location
' only search in the Location column
With Range("Table1[" & 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)
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"

End If

End With
End Sub

sp_AnswersTopicSeeAnswer See Answer
Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 627
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
2
March 4, 2021 - 10:11 pm
sp_Permalink sp_Print

Did you change the listbox's ColumnCount property to 7?

sp_AnswersTopicAnswer
Answers Post
Avatar
Stuart Allison

New Member
Members
Level 0
Forum Posts: 2
Member Since:
March 4, 2021
sp_UserOfflineSmall Offline
3
March 4, 2021 - 11:05 pm
sp_Permalink sp_Print

I knew it would be something obvious, but i didn't think it would be that obvious... Thanks, much apprecaited. 

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 627
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
4
March 5, 2021 - 12:31 am
sp_Permalink sp_Print

It's always the little things that are the easiest to overlook. 🙂

Avatar
Tushar Dahake

New Member
Members
Level 0
Forum Posts: 2
Member Since:
April 8, 2021
sp_UserOfflineSmall Offline
5
April 8, 2021 - 5:06 pm
sp_Permalink sp_Print

Hi,

I am facing the same issue with the column counts. I used the tutorial code to suit my needs. I have 40 columns in the table & i am not able to display more than 10. The code works fine till the column count is 10. I have given the coloumcount as 40 in the property box of the listbox as per you above suggestion.

But as soon as the below code is added, i get an error "Could not set the List Property. Invalid Property Value"

Results.List(RowCount, 10) = FirstCell(1, 11)

Where am i going wrong? Or is there a limit set to 10 columns?

In addition, I have 2 more queries:

1. Is there a way to show the column headers in the listbox

2. How to modify the code to search by date, as one of my columns has dates in it with DD-MMM-YY format

Thanks in advance.

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 627
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
6
April 8, 2021 - 7:23 pm
sp_Permalink sp_Print

If you use AddItem you are limited to 10 columns. The alternatives are to use an array and assign that to the control, or to use a range on a worksheet. Since you want column headers, the only simple option is a range on a worksheet and pass the address of the range to the Rowsource property of the listbox.

Avatar
Tushar Dahake

New Member
Members
Level 0
Forum Posts: 2
Member Since:
April 8, 2021
sp_UserOfflineSmall Offline
7
April 8, 2021 - 8:48 pm
sp_Permalink sp_Print

Thanks Velouria.

Could you help me with the syntax of how to do it? Since I am new to VBA, i am unable to do so.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Shanna Getschel, andria young, RAMEZ ATTAR
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 205
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Bruce Tang Nian
Scot C
Othman AL MUTAIRI
Misael Gutierrez Sr.
Attif Ihsan
Kieran Fee
Murat Hasanoglu
Brett Dryland
Saeed Aldousari
Bhuwan Devkota
Forum Stats:
Groups: 3
Forums: 24
Topics: 6222
Posts: 27293

 

Member Stats:
Guest Posters: 49
Members: 31918
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.