VBA autocomplete text in combobox

Question

I am creating a combobox for searching sheets using sheet name with autocomplete functionality, I am able to get the name of the sheets of a workbook in dropdown list but when i am typing few alphabets of the name of sheet, i want to display suggestions of all the sheets present with the alphabets i have written in the combobox.

Below is the code done till now, with which i am able to select the sheet name and nagivate to that sheet.

1st - Add a combobox into first sheet and name it (I called it cmbSheet). I used an ActiveX Combobox (in Excel 2007, under Developer tab).

2nd - Opened VBA and added the below code into workbook code. This code will populate the combobox with the sheet names every time the workbook is opened.

Private Sub Workbook_Open()
    Dim oSheet As Excel.Worksheet
    Dim oCmbBox As MSForms.ComboBox
    Set oCmbBox = ActiveWorkbook.Sheets(1).cmbSheet
    oCmbBox.Clear
    For Each oSheet In ActiveWorkbook.Sheets
        oCmbBox.AddItem oSheet.Name
    Next oSheet
End Sub

3rd - Now, i went to the code of my first sheet (where the combobox has been added) and added the code that will activate the sheet chosen in the combobox. The code is:

Private Sub cmbSheet_Change()
    ActiveWorkbook.Sheets(cmbSheet.Value).Activate
End Sub

Now, when the combobox value changes, the respective sheet is activated.

If the name of the sheets are dynamically changed, then the above link won't work. I don't want to use : With Me.ComboBox1 .AddItem "bat" .AddItem "battleship" .AddItem "battle" .AddItem "batty" .AddItem "bathhouse" End With — RTR, Dec 17 '15 at 13:06
completely wrong link... sorry... http://trumpexcel.com/2013/10/excel-drop-down-list-with-search-suggestions/ should give you a hint (just do it completely in vba)... there was a different link doing exactly what you looking for, but i cant find it again :( — Dirk Reichel, Dec 17 '15 at 13:40

score 1 · Answer 1 · edited May 08 '18 at 21:49

This solution solves part of your problem: the issue with dynamically changing sheet names.

Move the combobox fill procedure into its own method. Then call the method from the Workbook_Open sub. Every time you use a VBA procedure to change worksheet names, add the line Call RefreshList to the end of the procedure.

    Private Sub Workbook_Open()
        Call RefreshList
    End Sub

    Private Sub RefreshList()
        Dim oSheet As Excel.Worksheet
        Dim oCmbBox As MSForms.ComboBox
        Set oCmbBox = ActiveWorkbook.Sheets(1).cmbSheet
        oCmbBox.Clear
        For Each oSheet In ActiveWorkbook.Sheets
            oCmbBox.AddItem oSheet.Name
            Next oSheet
    End Sub

    Private Sub DynamicallyChangeSheetNames()
        'Do stuff, change a sheet name
        Call RefreshList
    End Sub

To handle someone manually renaming sheets, check out https://stackoverflow.com/a/1941199/5103770 as well as http://www.cpearson.com/excel/RenameProblems.aspx.

VBA autocomplete text in combobox

1 Answers1