Excel 2007 Userform Combobox Dropdown doesn't select on click

[dean12]

Excel, you're killing me! I originally posted thread 707-1651774 about a year and a half ago and I still get bitten by VBA.

So I have 2 pairs of combo boxes: SI & Step and Proj & Task

I populate comboSI.
The user selects an item.
In the AfterUpdate event I populate ComboStep. What goes into ComboStep varies depending upon the user's selection - that's why the delay to populate until comboSi.AfterUpdate.
The user clicks the dropdown arrow on ComboStep and selects an item.
Cool.............everything works.

A little further down the userform I have the other pair ComboProj and ComboTask. I go through the same routine - the user selects an item in ComboProj, I populate ComboTask, etc. The difference is that when the user clicks the dropdown arrow on ComboTask and selects an item we get nothing. The combo box remains empty. I can click the dropdown arrow again to repeat my selection and now I get a value populated into ComboTask.


I've built a tracer that displays events executing (basically displays the routine names). It goes:

ComboPlan_AfterUpdate
populateComboTask
ComboPlan_Exit
ComboTask_Enter
ComboTask_DropButtonClick
ComboTask_DropButtonClick

It executes the DropButtonClick event twice and we have no selection. Now a weird thing. If I put into the DropButtonClick event this code:

msgbox comboTask.listcount

Then the DropButtonClick event fires, the message box displays the count (it is 8), now we get a new sequence:

ComboTask_Change
ComboTask_Click
ComboTask_DropButtonClick

and bingo we have a value in the combo box control. I promise - the only change was to add the MSGBOX statement.

 

[PHV]

I'd try to add a call to DoEvents at the end of populateComboTask.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dean12]

Yea, good idea but it doesn't seem to help. I tried DoEvents in a couple of different places

ComboProj.AfterUpdate
populateComboTask

but no change. And the crazy thing is there are items in the combobox, I know that for usre, and Windows highlights the item as I mouse over it but click yields nothing.

 

[Markus4]

Hi,

just a wild guess: does the caption of your combos contain an ampersand &?
I've experienced the follogwing: when I define a style with an ampersand in its name (e.g. "Anrede & Gruß") and use it as category for creating autotexts, the autotexts exist. They show in the autotext menu list box, but disappear in the corresponding control bar dropdown - only the category shows. (I also checked the German letter "ß", but this behaves normal.)
I have no idea why this happens. I observed this from Word97 to WordXP - no change.

HTH anoyway.

Markus

 

[dean12]

I'll check but I don't think so.

 

[dean12]

Continuing...I cut everything off the userform except for the proj & task fields. Cut the code down to the bare minimum. It still fails. There are only the two controls on the form. For what it's worth the entire code set for the user form is:

Option Explicit

Private Sub ComboTask_AfterUpdate()

    MsgBox "ComboTask_AfterUpdate ListCount,ListIndex=" + CStr(Me.ComboTask.ListCount) + " / " + CStr(Me.ComboTask.ListIndex)

End Sub

Private Sub UserForm_Activate()

    Call populateComboPlan

End Sub

Private Sub populateComboPlan()

    Dim fRange                                   As Range
    Dim c1                                       As String
    Dim cel                                      As Range

    Me.ComboPlan.Clear

    With Me.ComboPlan
        c1 = "CC1:CC51"
        Set fRange = Find_Range("0x0000030000000001", shLis.Range(c1), xl_Values, xl_whole, False)
        If Not fRange Is Nothing Then
            For Each cel In fRange
                .AddItem shLis.Cells(cel.row, "CB")
                .List(.ListCount - 1, 1) = shLis.Cells(cel.row, cLis_Pln_PSI_Plan_Id)
            Next cel
        End If
    End With

End Sub

Private Sub ComboPlan_AfterUpdate()

    Call populateComboTask

End Sub

Private Sub populateComboTask()

    Dim strkey                                   As String
    Dim fRange                                   As Range
    Dim c1                                       As String
    Dim cel                                      As Range

    Me.ComboTask.Clear

    With Me.ComboTask
        strkey = Me.ComboPlan.List(Me.ComboPlan.ListIndex, 1)
        c1 = "CS1:CS799"
        Set fRange = Find_Range(strkey, shLis.Range(c1), xl_Values, xl_whole, False)
        If Not fRange Is Nothing Then
            For Each cel In fRange
                .AddItem shLis.Cells(cel.row, "CR")
                .List(.ListCount - 1, 1) = shLis.Cells(cel.row, "CQ")
                .List(.ListCount - 1, 2) = cel.row
            Next cel
            If .ListCount = 1 Then
                .ListIndex = 0                                         ' 1 task so default to it
            End If
        End If
    End With

End Sub

Private Function Find_Range(Find_Item As Variant, _
                            Search_Range As Range, _
                            Optional LookIn As eLookin, _
                            Optional LookAt As eLookat, _
                            Optional MatchCase As Boolean) As Range

    Dim c                                        As Range
    Dim FirstAddress                             As String
    Dim LastCell                                 As Range

    If IsMissing(LookIn) Then LookIn = xlValues                        'xlFormulas
    If IsMissing(LookAt) Then LookAt = xlWhole                         'xlWhole
    If IsMissing(MatchCase) Then MatchCase = False

    With Search_Range
        Set LastCell = .Cells(.Cells.Count)

        Set c = .Find( _
                what:=Find_Item, _
                LookIn:=LookIn, _
                LookAt:=LookAt, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, _
                MatchCase:=MatchCase, _
                SearchFormat:=False, _
                after:=LastCell)
        If Not c Is Nothing Then
            Set Find_Range = c
            FirstAddress = c.Address
            Do
                Set Find_Range = Union(Find_Range, c)
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress
        End If
    End With

End Function

The unfortunate thing is that I go and start deleting other userforms, code modules, etc. and eventually I get to the point where the issue at hand resolves itself and begins to work!

 

[dean12]

Yea, I'm really starting to think this is some sort of screwup within Excel and how it is storing stuff. I started deleting userforms, retrying the combo boxes, and got to a point where it worked. So I backed up to my starting point and deleted that userform which caused things to work and lo, it did not work. So it's not just that single userform but some combination of things.

I have used a tool called VBA Code Cleaner (Build 5.0.32) and while I'm sure this is a good tool it is not helping in this case. Any thoughts on something else which might for Excel to rearrange and maybe clear this error?

 

[dean12]

Well heck, export the userform, import, and it works. I could swear I tried this. Blasted Excel.

 

[dean12]

Aw, spoke too soon. It doesn't work after the export/import. I guess I must have clicked the task combobox twice by accident. That's one of the strange symptoms. Click the drop down and select and you get nothing. Click again and select and it works. OR: click in the text area of the combo box then click the drop down to select and it works. It's kinda like the control never becomes the active control.

One way or the other, still broke.