Searchable drop-down list in Word

[max76]

Hi everyone,

I need your support to get a searchable drop-down list to work in Word as it does in Excel. I transferred the VBA code from Excel to the Word file (see attached filed).
I added some coding in the modMain module to point to the Excel workbook from Word. When I click on the button on Word to trigger the Main module, the userform is opened but no list is generated when I start typing text in the textbox above.
I tried to debug the macros and I found the error in the UserFormBook shown in the photo attached.

Can somebody shed some light on the issue that prevents the list from being populated in Word?

Thank you in advance for your help!

Regards
MassimoSearchable dropdown list.zip

[gmayor]

Word is not Excel and thus does not work in the same way, as you have no doubt discovered. The attached is an approximation of what you want to achieve.
The worksheet has been sorted on column A
Items in the list will contain the text that you type in the text box.
The macro expects to find the workbook in the same folder as the template, otherwise you will have to select the workbook, worksheet and column.

[max76]

Hi Graham,

First of all thank you soooo much for your message and the solution you offered . I had been looking for something like this for months! I'm reaching out to you to verify if a couple of tweaks are feasible so that the solution can fit my purpose even better. While the Excel file I attached previously was just an example to simplify the scenario, I've now attached the huge file with many worksheets that I'll be using for real. Please find below a couple of points for your consideration:
Errors.zip

1. The Excel workbook will be always the one that I have attached and will be always positioned in the exact same path of the Word's file template, so there is no need to manually select the workbook each time. So basically the file name will always be "ActiveDocument.AttachedTemplate.Path & "\Errors.xls". I'm wondering if there is a way to directly use this as the source file and thus remove the content control on the userform to choose the workbook
2. The worksheet name will be always the value of the string called Tag.
3. I did not fully understand the reason for having to select the header of the column each time. Would it be possible instead to automatically choose cell "A1" at the begining of each worksheet to set the start of the range to create the list of items from?
4. As you can see in the screenshot a few items are cropped as though the height of the Excel row is reduced. Is there a way to fix that?Screenshot.jpg
5. Instead of having a blank list of items shown upon opening the userform, would it be possible to get the full list of items of a worksheet and THEN have the option to narrow down that list by starting to enter text in the textbox?

Thank you very much in advance for taking my suggestions into consideration.

Best regards from Italy
Massimo

[gmayor]

Unfortunately you did not attach your workbook, however your changes are simple enough.

The default workbook and worksheet are selected from sWB and the two combobox list indices. Here the first items in the lists after the prompt.

The column box displays the first item in the list because you don't have a header row in your worksheet. The column selected is the column displayed. This will be selected automatically if the workbook and worksheet are configured with your preferred defaults.

Public Sub Main()
    Dim sWB As String: sWB = ThisDocument.Path & "\searchable.xlsm"
    With frmExcelUserForm
        If FileExists(sWB) Then
            .TextWorkBook.text = sWB
            .ComboWorksheet.ListIndex = 1
            .ComboField.ListIndex = 1
        End If
        .show
    End With
    Unload frmExcelUserForm
End Sub

To start with the listbox filled with all the data, enable the lines commented out below.

Private Sub ComboField_Change()
    ' If ComboField.ListIndex > 0 Then
        ' xlFillList ListRecords, _
       ComboField.ListIndex, _
       TextWorkBook.text, _
       ComboWorksheet.text, True, True, ComboField.text
    ' Else
         ListRecords.Clear
    ' End If
    lbl_Exit:
    Exit Sub
End Sub

There's not much you can do with the cropping apart from making the listbox and form wider.

If you have any problems, post the workbook.

[max76]

Hi Graham,

Thank you for your follow-up. Please find attached the huge Excel workbook with many worksheets in it. I realized that this file had been attached as an inline file in my previous message. In the same ZIP file please find attached the code that I had been using so far to populate a combo box from the same Workbook. As you can see, the worksheet to populate the list is the value selected in the drop-down list (for sake of simplicity I only entered a few items such as ID, Passport, Schedule A, etc.) without any need to manually select the header. Basically I need to get rid of the content controls marked below (and the corresponding code):

Clean up.jpg

In addition, as you can see in the Test.dotm file, the items in the list box are not cropped vertically. Please compare the 2 screenshots below:

Cropped.jpg

Is it possible to integrate the code I used in the Test.dotm file with yours so that those issues are resolved?

In addition, I also used a code to use the mouse wheel to scroll down and up the items in the list.

Last thing, I was wondering if it is normal that when I type something in the text box to narrow down the list, the "repopulation" of the list is not quick and smooth and I need to wait a few moments before the strokes that I type appear in the box. Please see a short animation included in the ZIP file attached to get a better idea of the behavior I described.

Thank you so much again for your great support!

Regards
Massimo

[gmayor]

I have modified the userform and code to only present the prompts when the workbook doesn't exist in the same folder as the template.
I have made the list box wider.
The population of the list box requires the worksheet to be re-read for each change you make in the text box. With a very large worksheet it will take some time to read the data - hence the delays compared with the simpler test worksheet.
I'll leave you to make the rest of the changes.
If you want it faster you will probably have to work solely in Excel.

[max76]

Hi Graham,

Thank you again for your help! However, I still can't figure out one thing. When triggering the userform, the list box is populated with the items from the first workshetet (ID) which is not the purpose of my macro.

In the Word file that I had attached in my previous email, you can find a content control under the arrow with items matching the exact names of a few worksheets in the Errors.xls file (I only put a few to simplify). So the list box must be "dynamic" so to speak and get the worksheet name from the value in that content control (I know how to retrieve the value but I don't know where to put that code). Could you please show me where I can do that in your code? I need the worksheet populating the list box to be referred to as a string value rather than an index.

I also noted that the first row in the worksheet is skipped I thought that it was possible to select that the columns have no header.

Items.jpg

Thank you

Regards
Massimo

[gmayor]

I have added code to enable the form to be reset so you can choose the workbook/sheet/field and whether there is a header row. The selections are stored in the registry for re-use at
HKEY_CURRENT_USER\SOFTWARE\VB and VBA Program Settings\SearchableWS
There is no limit to the amount of customisation that can be achieved, but there is a limit to the amount of time I am willing to spend on this. All the functions and methods you need to make changes are in the code.

[max76]

Hi Graham,

Thank you for your email. I didn't mean to bother you, I apologize if I did. I'll try to figure out myself how to tweak the programming code as the solution offered so far covers only 80% of my needs and therefore cannot be used as intended. Too bad that Microsoft makes apparently easy things difficult to accomplish

Thank you for your support

Regards
Massimo Ollani

[max76]

Hi Graham,

I just wanted to thank you once again. I was finally able to tweak your great code to cover all my needs and the macro works perfectly now!

Thank you again for your time and efforts. That was much appreciated.

Regards
Massimo