Typing in a cell to make auto-suggestions for a drop down box

[cragleaper]

Hi guys,

So I've been working on something for fun and trying to create my own nutrition tracker...

Whilst now many people play with excel for fun, I'm one of the sad few

So I have preloaded a sheet with nutrition data for various foods and am using lookups to pull the data through which is working just fine.

I've got a dynamic list, where you type in the cell above and press enter, the list updates and you can copy and paste the item into your meal that you're trying to fill so that the lookups can start working.

It may not be possible but I thought I'd ask the question:

What I'd rather do is as I'm typing into a given cell the list updates so I don't have to bother with the copy and paste...

so for example if you start typing app

then it would suggest pineapple, apple etc...

I'm sure there's a clever way of doing this, it feels like excel would have this functionality somewhere.

However, I've googled and found VBA to do this or found ways to do it without VBA, the issue being these solutions are fine if you're only doing it with one cell, however I'm potentially running it for 30 cells and this would make the sheet majorly cumbersome...

so over to the collective insight of the internet

I'm also happy to post the spreadsheet if people want to have a look, but I'm not sure how to do this...

Thanks in advance for any suggestions

Alex

 

[Jeffrey Mahoney]

hey cragleaper. BTW, are you a climber or a parkour-ist?

I don't believe there is a way for Excel to detect changes in a cell as you are typing. You could use a textbox on a form. It will get an event every time you enter a new character. A Change event occurs, you test for similarities in your database and put the results in a listbox. Now, that said. If your database is large, you will notice a slowdown as you type.

Jeff

 

[cragleaper]

hey cragleaper. BTW, are you a climber or a parkour-ist?

I don't believe there is a way for Excel to detect changes in a cell as you are typing. You could use a textbox on a form. It will get an event every time you enter a new character. A Change event occurs, you test for similarities in your database and put the results in a listbox. Now, that said. If your database is large, you will notice a slowdown as you type.

Jeff

Hi Jeff,

Thanks for the response, I'm a climber, I've not got the fitness for parkour unfortunately

So I'd looked into the textbox on a form which would work for one cell which is brill!

However I've got a table like this:

Breakfast
Column A Column B
Banana Lookup to pull back calories
XXXXX1
XXXXX2
XXXXX3


so where the XXXX* are I'd like to use drop down boxes, however there's over 300 entries in my list of foods that I have the calories for, which is impractical, so that's where the idea came from. It feels like something that Excel should be able to do but hey ho This kind of table is repeated for lunch dinner snacks etc so all in all there's a potential of about 80 cells.

OK so thinking about it... is there a way to point the combo box that you mentioned above at a different cell every time so that it applies the data to the cell I required. Then it would save on bloat and would save me a little time?

So for example

(Dropdown)
I point it at A3 - select the one I want and it pastes the data in there, then the next cell I point it at C4, select something different and it pastes it in there??

Thanks for your input

Alex

 

[Jeffrey Mahoney]

I know this is simplistic, and I'm not sure how you laid out your worksheet. I created a combobox on a sheet. I tied it to Cell E13 and the listfillrange is a column of data. When I change the selection in the combobox and event happens. This event allows you to do something with it. In the case below, I just created some code to place the new selection at the end of a list in Column A. It always places the value in the next empty cell in column A.

Private Sub ComboBox21_Change()
  Range("A" & Cells.Rows.Count).End(xlUp).Offset(1, 0).Value = Range("E13").Value
End Sub

 

[cragleaper]

Thanks for that, that's perfect I can work with that.

I'm not too hot on the VBA side of things!

Thanks for you help again