Search box that allows you to start typing and it will autofill the answer matching file names

[dpaton05]

I have a spreadsheet that I want to have something that allows me to start typing a name and it will auto fill as I type to match the name typed with the names on other spreadsheets that are kept in a folder called Young People that is within the folder of the spreadsheet. I am not sure how to do this, could someone give me some guidance please?

After enough letters are typed in and the correct name appears, I want to be able to press a button and have the relevant document open.

Thanks

 

[Michael M]

 

[dpaton05]

Thanks Michael, I just had found it.

Ok, so now I have worked that bit out. I have a drop down list that autocompletes from a list stored on another worksheet.

Now I need some way to open a file on the young person once I have the correct name. Maybe a button underneath that I have to select. The files for the young people will be called the same as the names from the list and will have have the same directory structure as described in post 1.

Although, what I would really like is to press enter and for the other spreadsheets to open.

 

[dpaton05]

I have tried to write some code to open the files from the item selected but it said that the file was not found. What is wrong with my code?

Sub OpenFile()
    Dim dd As DropDown, SelectedItem As String
    
    Set dd = ActiveSheet.Shapes(Application.Caller).OLEFormat.Object
    SelectedItem = dd.List(dd.ListIndex)
    
    Workbooks.Open Filename:=ThisWorkbook.Path & "\Young People\" & SelectedItem & ".xlsm"
End Sub

 

[Michael M]

I believe You need to refer to the cell that is populated, not the list itself.
If the linked cell is say A1, use

Sub OpenFile()
    Dim SelectedItem As String
    SelectedItem =cells(1,1).value
   Workbooks.Open Filename:=ThisWorkbook.Path & "\Young People\" & SelectedItem & ".xlsm"
End Sub

 

[dpaton05]

But it is not a cell that gets populated, the combo box does.

 

[dpaton05]

Actually, I worked that out Michael, thanks for your help

 

[dpaton05]

How would I do it if I wanted the code to execute upon pressing enter while typing in the combo box?

 

[Michael M]

USe a change event

Private Sub TempCombo_Change()
Call MyMacro
End Sub

 

[dpaton05]

Wouldn't That call the sub for each keystroke, not just pressing Enter?

 

[Michael M]

did you try it ??
I'm guessing without testing that the new change to the combo box won't take effect until you enter.