Search Any Keyword in Drop Down List

[jmckinley]

Hello - I'm new to the forum. There is such a plethora of information here and I'm enjoying learning new things already. I joined to ask a specific question, however, if it has been previously answered please feel free to redirect my attention.

I created a time tracking worksheet for my company. In this time tracking worksheet there is a list of project names which I've created and made searchable through the use of data validation and a query. Currently, you begin in a cell by typing the Project number, however, my boss would like to be able to type any keyword related to the project and have the selection auto-populate. For example, the list might contain names such as:

IT1 Jones (Hess)
IT2 Smith (Walter)
IT3 Rutan (Rowe)
IT4 Fields (Dube)

Currently, you can search by typing "ITP1", "ITP2", "ITP3" etc. etc. However, my boss would like to be able to type "Smith", or "Rowe" and have the selection auto-select and/or auto-populate. I'd also like to have a visible scroll bar.

Again, not sure if this is the right forum but any assistance would be greatly appreciate. I consider myself an intermediate level user of Excel and am pleased with the success of this project thus far. It's just these last few nit picky things that I would like to adjust that are driving me bonkers.

Please let me know if any questions.

Thank you,

Jessica

[Keebellah]

Hi Jessica, welcome to the forum.
There are several ways you could do this and even without macro coding VBA.
To make it easier for any of us to help you I would advice you to upload a sample file with the table lay-out you're using and some dummy non-sensitive data.
Do you have any experience with macros, VBA code?

[jmckinley]

Hi Keebellah,

Thanks for your help on this. I have minimal experience with macros and VBA code but am learning and loving it more every day.

I'd like to include a test version, however, when I try to upload I'm told the file is too large. I tried reducing the size by converting to binary, no such luck. Is there any other way to get the file to you?

Thanks again.

[Keebellah]

Hi Jessica,
Have you tried zipping the file?
Read my PM

[Keebellah]

Hi Jessica,
Here's your file, resized so it will fit. I have attached it since there is no sensitive information in the data.
You should try and avoid over 1 million rows with formulas.
I reduced it to 10000 rows so it 'fits' and opens without any issues.
I'll see if I can can help
Always check why a file is 'suddenly' over sized, press Shift+Ctrl+End in each worksheet and it will take you to the last filled row and columns

[Keebellah]

First of all: What are you trying to do here?

I reduced it to a variable LastRow but what does it invoke, doesn't work here but that clear because I do not have the refresh connection which seems to be the the same file but I have to figure it out.
And where do you want the search to show up, at which moment?
Could you please explain this? It overwrites the active cell in A or whatever you have as active cell with the now() value

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim lastRow As Long
lastRow = Range("A" & Rows.Count).End(xlUp).Row + 1

If Not Intersect(Target, Union(Range("D2:D" & lastRow), Range("E2:E" & lastRow), Range("A2:A" & lastRow))) Is Nothing Then
    Cancel = True
    Target.Formula = Now()
End If
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet

Set cboTemp = ws.OLEObjects("TempCombo")
  On Error Resume Next
  With cboTemp
  'clear and hide the combo box
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
  End With
On Error GoTo errHandler
  If Target.Validation.Type = 3 Then
    'if the cell contains
      'a data validation list
    Cancel = True
    Application.EnableEvents = False
    'get the data validation formula
    str = Target.Validation.Formula1
    str = Right(str, Len(str) - 1)
    With cboTemp
      'show the combobox with the list
      .Visible = True
      .Left = Target.Left
      .Top = Target.Top
      .Width = Target.Width + 5
      .Height = Target.Height + 5
      .ListFillRange = str
      .LinkedCell = Target.Address
    End With
    cboTemp.Activate
    'open the drop down list automatically
    Me.TempCombo.DropDown
  End If
  
errHandler:
  Application.EnableEvents = True
  Exit Sub

End Sub

[Keebellah]

I am missing the file named 'FS Projects Drop Down List.xlsx'this where you pick the data and dropwonlist

[Keebellah]

Figured it out, the connection is to the worksheet in the same file. Since you probably copied it to mail the reference remains to the original file.
In these cases you should open the file and select save as to keep the connections valid.

Okay, the doubleclick is entirely wrong since you have three columns you check on but the first (in A is not related to D or E.
There is no explanation what you want the user to do.
Pick an item from the dropdown list?
I think that a a richt click (or double click) to show a userform with the possible choices and a fill field to select while typing would be the best.
Will work on that (based on my assumption of what you want) and hope I'm on the right track.

[Keebellah]

My idea.
Column B row x must not be empty, so first select a code in column B from the dropdown list there
Then select column C and right click in the user form you can either select an item in the listbox or start typing in the textbox above select the item in the listbox and press commit.
The Item in the listbox selection MUSt be doen, I did not add any tests for that.
The listbox will show the item(s) that contain the text typed in the textbox above

[Keebellah]

Some (minor) modifications explanation in textbox in the TimeSheet worksheet

[jmckinley]

Thank you for the feedback. I greatly appreciate the time and effort you've put into this.

To answer your question: My colleagues and I perform project-based work. Currently, we have upwards of 100 projects going. When we receive a new project I add the project name to our drop-down list in the worksheet. Every we day as we are working on projects we log into this worksheet to track our time. I want to simplify the use of this worksheet by allowing for double-click entry of the date and time. For the drop-down list, I want to be able to type any part of the name of the project to select the correct entry. I want the search to show up in column C and the user should be able to either pick an item from the drop down list, or, start typing the name or number of the project and have the list auto-populate. I hope that information helps.

Thank you again.



"First of all: What are you trying to do here?

I reduced it to a variable LastRow but what does it invoke, doesn't work here but that clear because I do not have the refresh connection which seems to be the the same file but I have to figure it out.
And where do you want the search to show up, at which moment?
Could you please explain this? It overwrites the active cell in A or whatever you have as active cell with the now() value"

"Okay, the doubleclick is entirely wrong since you have three columns you check on but the first (in A is not related to D or E.
There is no explanation what you want the user to do.
Pick an item from the dropdown list?
I think that a a richt click (or double click) to show a userform with the possible choices and a fill field to select while typing would be the best.
Will work on that (based on my assumption of what you want) and hope I'm on the right track."

[Keebellah]

Clear, but, can you work with the file as it is now?
Type and select as you go is not possible in the dropdown list (as far as I have seen 'til now) but maybe the properties can be changed
Will see if I find something, but I doubt it.
The way it works now with selecting a value in column B and the opening the form for Column C seemed like a nice trick

[jmckinley]

I agree, selecting a value in column B and the opening the form for Column C is a great trick. I can work with the file as it is now. I'll look at the properties also and see if anything can be adjusted.

[jmckinley]

This works so well! I'm eternally grateful for your help.

[Keebellah]

Glad to have been able to do so