Refreshing a ComboBox Issues

[rjn2002]

Hi all,

I am trying to populate an ActiveX ComboBox on an Excel spreadsheet with file names from a given directory. Currently I have a "refresh" button that users can click to refresh the drop-downs when they add new files to the folder. I would like to automatically do this.

I have tried just about all of the even properties with no success.

In using GotFocus(), when the list refreshes, the drop-down does not fully expand. It's only after clicking the list again that you see the drop-down box fully expanded.

The dropButt*******() creates problems because it fires when the drop-down appears and disappears. Thus after the user selects an item, the list is cleared, thus the user can never actually select an item.

I have thought about checking the mouse position in the dropbutt******* event, and if it is not where the dropdown arrow is, then don't refresh the lists, but this is very risky, especially if I don't get the coordinates exactly correct.

I feel like I am closest with the GotFocus approach if I can just get the drop-down to fully expand after it triggers. Perhaps someway to trigger selecting it again to force it to fill the drop-down fully?

"cmbsWPM" is the name of the combobox I am working with.

Private Sub cmbsWPM_gotfocus()
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False

    cmbsWPM.Clear

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set dealdir = fso.getFolder("C:\SFW\moodys Macros\econ_files")
    Set deals = dealdir.Files
    
    For Each deal_file In deals
        If fso.GetExtensionName(UCase(deal_file.Path)) = "WPM" Then
        'Change path To suit
            cmbsWPM.AddItem (deal_file.Name)
        End If
    Next
    
    On Error GoTo 0
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
End Sub

 

[rjn2002]

Does anyone know what is going on here?

 

[rjn2002]

I was able to get this to work by adding the following code, though I am afraid if I add more to the other methods, this could be dangerous:

Private Sub cmbsWPM_DropButt*******()
    DoEvents
End Sub