2

I have an excel sheet for groceries. One column is the name, another is whether to buy it or not (checkbox) and another is the amount.

I'd like to have a widget in the top row so that I start typing an item's name and it shows a list of matching items that I can select from, or if I continue to type and there's only one item, completes its name. When the last item is selected, other widgets show the amount, which I can edit and clicking 'check' will check the item in the list.

I know this is kind of very specific, but am hoping someone can at least get me started.

Thank you, Ittay

1
  • I don't think there's any functionality close to a dynamic search suggestions in excel. At best, excel will suggest the first matching item, or a drop-down of choices in Data-validation. It may be possible to implement one in VBA, but you will need to intercept every keystroke (up, down, enter, etc.) and program your own dynamic suggestion box (a small form). So a very manual and cumbersome endeavor. Likely not worth the trouble at all. It will be interesting to see if someone has attempted this before.
    – mtone
    Dec 17, 2011 at 20:29

2 Answers 2

1

For your list of matching items, if you want it to autocomplete, you're going to need to use a combobox + a macro.

First step is to create a list of groceries on your sheet or another worksheet. This list will be used to populate the "widget". You should make this list a Named Range to make your life a little easier later.

Then comes your "widget". You didn't specify which version of Excel you have, so you're going to need to find out how to add an ActiveX Combobox to your sheet.

Once you've done that, you'll need to open Visual Basic Editor - press Alt + F11 to do so.

In the left window, you'll see VBAProject([YourWorkbookName]) and folder under it called "Microsoft Excel Objects".

Expand that folder so that you see something called ThisWorkbook. Double click that and, in the right window, place the following code:

Private Sub Workbook_Open()
    Sheets("Sheet1").ComboBox1.List = _
      Application.WorksheetFunction.Transpose(Range("A1:A56"))
End Sub

Replace:

  • ComboBox1 with the actual name of your combobox (this is the default for the first one inserted into a workbook)

  • Sheet1 with the name of your actual sheet

  • Range("A1:A56") with the location of your list of groceries. If you have it on another sheet, you can say Sheets("Sheet2").Range("A1:A56"). If you've given the range a name, such as "Groceries", you can replace it with just Range("Groceries") (named ranges are global).

What this does is tell Excel to populate that combobox with the list you tell it to when you open the workbook (you can check if it works by clicking the play button in VB Editor's toolbar). You'll need to have the option to enable macros.

I'm sure this will keep you busy for now :)

0

I would create a list of items you generally purchase on another sheet, then use data validation to put a drop down in the item column with all the items.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .