Search data then show results in a list box

[kira023]

Hi,

I'm currently working on a troubleshooting guide for our team and I've created a userform to be able to search using the 2 combo boxes that are dependent with each other. When clicking the Search button I wanted it to use the information on the 2 combo boxes to find it in a single worksheet where all the data is located (eg. Bot Names = column B and automation name = column C) and then show the troubleshooting guide in column D on a list box which corresponds to the data being searched by the 2 combo boxes.

I don't have an initial code for the search button. If someone can please help me create this that would be helpful.

Thank you

 

[Fluff]

How is your data laid out?
Is it anything like

Excel 2013/2016
	B	C	D
2	Somerset	Mendip	Street North
3	Somerset	Mendip	Street West
4	Somerset	Bath and North East Somerset	Farmborough
5	Somerset	Bath and North East Somerset	Westmoreland
6	Somerset	Bath and North East Somerset	Twerton
7	Somerset	Bath and North East Somerset	Widcombe
8	Somerset	South Somerset	Yeovil Central
9	Somerset	South Somerset	Yeovil East
10	Somerset	South Somerset	Cary
11	Somerset	South Somerset	Ivelchester
12	Somerset	Bath and North East Somerset	Timsbury
13	Somerset	Bath and North East Somerset	Westfield
14	Somerset	Mendip	Chewton Mendip and Ston Easton
15	Somerset	Mendip	Coleford and Holcombe
User2

Where col B is combo1 value, col C is combo2 & cold D is what needs to go in the listbox?

 

[kira023]

Hi fluff,sorry for the late reply.yes that is the same format as my spreadsheet. I just need to know how to do it on the conditions that i wanted.thank you

 

[Fluff]

Ok, how about

[COLOR=#ff0000]Option Explicit
Dim ufDic As Object
[/COLOR]
Private Sub ComboBox1_Click()
   Me.ComboBox2.Clear
   Me.ListBox1.Clear
   Me.ComboBox2.List = ufDic(Me.ComboBox1.Value).Keys
End Sub

Private Sub ComboBox2_Change()
   Me.ListBox1.Clear
   Me.ListBox1.List = ufDic(Me.ComboBox1.Value)(Me.ComboBox2.Value).Keys
End Sub

Private Sub UserForm_Initialize()
   Dim Cl As Range
   Dim Ws As Worksheet
   
   Set Ws = Sheets("[COLOR=#0000ff]Data[/COLOR]")
   Set ufDic = CreateObject("scripting.dictionary")
   ufDic.CompareMode = 1
   For Each Cl In Ws.Range("B2", Ws.Range("B" & Rows.Count).End(xlUp))
      If Not ufDic.Exists(Cl.Value) Then ufDic.Add Cl.Value, CreateObject("scripting.dictionary")
      If Not ufDic(Cl.Value).Exists(Cl.Offset(, 1).Value) Then ufDic(Cl.Value).Add Cl.Offset(, 1).Value, CreateObject("scripting.dictionary")
      ufDic(Cl.Value)(Cl.Offset(, 1).Value)(Cl.Offset(, 2).Value) = Empty
   Next Cl
   Me.ComboBox1.List = ufDic.Keys
End Sub

The two lines in red must go at the very top of the module, before any code.
Change sheet name in blue to suit.

 

[kira023]

Hi Fluff,

Thanks for the code. I think it works fine but I wanted to tweak some of it. Is there a way to adjust the text shown on the list box? I can only see 1 line of what the list box shows. I wanted it to show like it is in a wrap text view like all the text that would show on the listbox are compressed.

and also can the data show until i hit the search button? because now it shows the text on the listbox immediately after I select the data on the combobox1 and combobox2.

I can see that I can't add attachments here. How can I send you the file?

Thank you so much!

 

[Fluff]

You can upload your file to a share site such as OneDrive, DropBox, GoogleDrive, mark for sharing & post the link to the thread.

 

[kira023]

I will upload the link to the file once I can use my personal laptop. There are strict security in my work so I'm not allowed to access file hosting sites.

Thank you so much

 

[Fluff]

Ok, that's fine.

 

[kira023]

Hi fluff,can you try to access this link?https://drive.google.com/file/d/1KXAXUufiZ3Cvy1lG67hAF1LWUjV_Ggzf/view?usp=drivesdklet me knw if you can access the file.thank you

 

[Fluff]

I can see the file thanks, but can you please explain to me, why I should continue helping you, considering the fact that you have now posted this question to another site as well?


Cross posted https://www.excelforum.com/excel-pr...-excel-search-and-find-macro.html#post5094826

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.