• 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

Search and display |VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / Search and display |VBA & Macros|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
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 & MacrosSearch and display
sp_PrintTopic sp_TopicIcon
Search and display
Avatar
Matthew Pitt
Member
Members
Level 0
Forum Posts: 7
Member Since:
April 2, 2022
sp_UserOfflineSmall Offline
1
April 2, 2022 - 4:23 pm
sp_Permalink sp_Print

Hi all,

 

I am currently recreating a document control spreadsheet and wanting to add some search function through the use of macros and VBA.

I have used the "Display All Matches from Search in Userform ListBox? guide from this website but I am wanting to go a little further with it and everything that I have tried will not work.

 

The extra steps that i would like to add are as follows,

1. Column heads from my worksheet page.

2. the ability to select a result from the listbox and then click another bottom to show the data from each column in its own textbox. The idea of this is to have limited information in the search field but more information in the show form.  

 

i.e on the search field, the document name, number version number and document status will be shown . When you select the document you want and then click the show button you will be shown the same data as the search field but additional information such as the document reviewer, date of review, validity of the document, where the document belongs.

 

I have added pictures of what I am trying to explain.  

 

Thank you for any help that you can provide.

CheersSearch-Form.JPGImage Enlarger

Document-Details.JPGImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage Search-Form.JPG (25 KB)
  • sp_PlupImage Document-Details.JPG (37 KB)
sp_AnswersTopicSeeAnswer See Answer
Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOnlineSmall Online
2
April 4, 2022 - 5:04 pm
sp_Permalink sp_Print

Hi Matthew,

What do you mean with: "1. Column heads from my worksheet page."

See if the attached search form helps, where you can select the column where the search will be performed.

Avatar
Matthew Pitt
Member
Members
Level 0
Forum Posts: 7
Member Since:
April 2, 2022
sp_UserOfflineSmall Offline
3
April 5, 2022 - 7:53 am
sp_Permalink sp_Print

Hey Catalin,

 

Apologies for my poorly written explanation of what I am trying to do. 

 

The column headers refer to the headers on the list box and having them the same as the column headers on the worksheet. I have uploaded a video to this reply which should explain this in a clearer manner. 

 

The Video also shows what I am trying to do in relation to the search and display functions. The attached picture is what I would like to display on the second userform. Thank you for your help.

Capture.JPGImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupMedia VBA-Coding_Trim.mp4 (1 MB)
  • sp_PlupImage Capture.JPG (43 KB)
Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOnlineSmall Online
4
April 6, 2022 - 3:31 pm
sp_Permalink sp_Print

Hi Matthew,
List column headings should be avoided, it is usually used when your list refers to a sheet range that HAS headers, this way the list reads headers from sheet.

You can do what I did in the form I sent, simply put bordered labels ABOVE the list, because in your case the list data does not come from sheet, it is written by code..

Avatar
Matthew Pitt
Member
Members
Level 0
Forum Posts: 7
Member Since:
April 2, 2022
sp_UserOfflineSmall Offline
5
April 7, 2022 - 5:55 am
sp_Permalink sp_Print

Hey Catalin,

 

That makes perfect sense. Thankyou heaps. 

 

Would you happen to know hoe I could create another userform to show all of the data for a document that is selected from the initial userform?

 

Thank you for your help.

 

Cheerse

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOnlineSmall Online
6
April 7, 2022 - 1:28 pm
sp_Permalink sp_Print

I think you want to know how to use the document selected to populate another form, not how to create a form, you seem to know how to create one.

Basically, when you populate that initial form with all results data, each entry from the search results list should have the sheet row information in a hidden list column (column width for the last column is 0).

Screenshot-2022-04-07-062009.pngImage Enlarger

Use then the List Double Click event to open that second detailed form , you will just have to pass the row number where the document info is located to a function that takes that row number and populates the form fields.

To get the selected entry from a list use:

'safety line:

If ListBox1.ListIndex =-1 Then Exit Sub ' no row selected

Dim Rw as Long

Rw=CLng(ListBox1.List(ListBox1.ListIndex,3))

As microsoft says:

The ListIndex property contains an index of the selected row in a list. Values of ListIndex range from -1 to one less than the total number of rows in a list (that is, ListCount - 1). When no rows are selected, ListIndex returns -1.

When the user selects a row in a ListBox or ComboBox, the system sets the ListIndex value. The ListIndex value of the first row in a list is 0, the value of the second row is 1, and so on.

sp_PlupAttachments Attachments
  • sp_PlupImage Screenshot-2022-04-07-062009.png (17 KB)
Avatar
Matthew Pitt
Member
Members
Level 0
Forum Posts: 7
Member Since:
April 2, 2022
sp_UserOfflineSmall Offline
7
April 8, 2022 - 4:06 pm
sp_Permalink sp_Print

Hey Catalin,

 

Thank you for you help and guidance with this coding. I do apologies for how useless I have been.

 

I have tried many different ways to get the code you sent through to work but I keep getting a syntax error. 

 

I have attached a demo version of the main document to this reply which contains all of the code and user forms.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1810
Member Since:
November 8, 2013
sp_UserOnlineSmall Online
8
April 8, 2022 - 4:28 pm
sp_Permalink sp_Print sp_EditHistory

To extract the row index from column 4 of the results list, you have to store it first there, when you search and populate the results list.

Screenshot-2022-04-08-092735.pngImage Enlarger

Then this code will work:

Private Sub showselected_Click()

If Results.ListIndex = -1 Then Exit Sub ' no row selected

Dim Rw As Long, Wks As Worksheet
Set Wks = ThisWorkbook.Worksheets("Forms")
Rw = Results.List(Results.ListIndex, 3)
DocumentDetails.TextBox2.Value = Wks.Cells(Rw, "A")
DocumentDetails.TextBox6.Value = Wks.Cells(Rw, "C")
DocumentDetails.TextBox3.Value = Wks.Cells(Rw, "B")

'continue with the rest of the textboxes needed

DocumentDetails.Show

End Sub

sp_PlupAttachments Attachments
  • sp_PlupImage Screenshot-2022-04-08-092735.png (78 KB)
sp_AnswersTopicAnswer
Answers Post
Avatar
Matthew Pitt
Member
Members
Level 0
Forum Posts: 7
Member Since:
April 2, 2022
sp_UserOfflineSmall Offline
9
April 9, 2022 - 3:11 pm
sp_Permalink sp_Print

Hey Catalin,

 

You are amazing!!

 

Thank you heaps for your knowledge and experience with helping me solve this coding.

 

Cheers 

 

Matt 🙂 

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Catalin Bombea, Ramon Lagos
Guest(s) 8
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:
stuart burge
Bruce Tang Nian
Scot C
Othman AL MUTAIRI
Misael Gutierrez Sr.
Attif Ihsan
Kieran Fee
Murat Hasanoglu
Brett Dryland
Saeed Aldousari
Forum Stats:
Groups: 3
Forums: 24
Topics: 6223
Posts: 27295

 

Member Stats:
Guest Posters: 49
Members: 31920
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.