Userform Combobox - Click event does not fire

[dwooldridge]

This problem has been driving me nuts for the last week. In Excel 2007. We have a userform with a bunch of controls. There are two pairs of combo boxes: SI & STEP are the first pair and PLAN & TASK are the second pair. By pair I mean the user selects an item in the 1st combobox and the 2nd is then populated based upon that selection and they then proceed to select from the 2nd combobox. The 1st pair (SI & STEP) work just fine. The 2nd pair do not.

It's hard to describe but its like the TASK combobox loses its focus or something. To reduce the problem down to a simple form I created a test case:

In the UserForm_Activate method I populate the PLAN combo then the TASK combo. No problems. Now I don't have to click PLAN at all and both boxes are loaded with data. Focus is on PLAN as it's the first tab stop.
I click downarrow on TASK, select an item and we are good to go. So I know this sample case works.

Now just to test my "lost focus" concept I put a MSGBOX statement into the PLAN_Exit event. Now when I click the down arrow on TASK I get the MSGBOX popup. When that closes I get the selection list for TASK and when I choose something (and I can choose) I get nothing in the combo box. If I immediately click the down arrow again and select then I do get a selection.

So you can see why I'm talking about it in terms of "focus" - its like after the MSGBOX the 1st down arrow cycle serves to bring us back to the TASK combobox and it takes a second down arrow to effect real action.

Now that's all fine and good but in my real application I don't have a MSGBOX and for the life of me I can't see that we have lost focus so maybe this "focus" idea is just a crude description of something different.

Sure would love to hear your thoughts as this is driving me nuts.

 

[dwooldridge]

With all due respect to everyone who has jumped in here - I just may have to take a look at the Change event as all of you have recommended.

Thinking out loud and looking at the trace of events I was doing it appears:

If we set ListIndex manually when we are not on the control we get a Change and Click event. This would be from some code where we say 'xxx.ListIndex = -1'

If we move focus to the control and select a value we get Enter, DropButt******* (if we used the drop down), Change, Click, DropButt******* (if we used the drop down), BeforeUpdate, AfterUpdate, and Exit.

If we move focus to the control and Tab through without changing the control value we get Enter and Exit.



Now setting ListIndex and getting Change & Click to fire is basically ok. But what about the process where we enter a value or select a value with the dropdown arrow. We get the Change and Click prior to BeforeUpdate/AfterUpdate. What happens if I choose to refuse the change in BeforeUpdate? Whatever code is embedded in Change or Click is going to have already executed.

As to the workbook availability. It is huge and as I cut out stuff I can get to the point where it starts to work so that's not doing anyone any good. I found that I could take the failing workbook in Office 2007 and send it to my Office 2010 machine and it would work. That makes me want to think that Microsoft fixed something related to this issue. I also suspect there might be a timing issue here. If I populate TASK in the Change event of PLAN then indeed things work. If I populate TASK in the PLAN Exit event then it doesn't work and it doesn't work if I populate in TASK Enter event. So there is some kind of timing issue I think. I have found all sorts of "timing" issues in Excel.

 

[dwooldridge]

OK, I cut this down quite a bit and it still fails on my machine running Office 2007. The workbooks is at:

https://www.box.com/s/9ae2v80khaf17fs10vwi

Load the workbook and 'right-click' below the yellow line on the Times worksheet. This will launch the form. Go down to Project, click the dropdown arrow and choose. Click the dropdown arrow on Task and choose. On my system nothing is selected. If I downarrow click and choose again the I get a value.

To get to the code 'right-click' above the yellow line and choose unlock. Then you can use Alt-F11 at will.

If anyone chooses to take a look at this issue and is able to point out where I am an idiot then I would really, really appreciate it as I have spent days trying to figure this out.

By the way I have found that I can continue to cut out code and it will eventually work. I don't have to touch the userform. I can cut out pivot tables and/or charts and that usually makes things start working again. I have also tried this on my Office 2010 system and it works fine.

 

[dwooldridge]

Well, I'm giving up on this issue. I've tried it seems a thousand different approaches and the problem seems now to come and go. I know that sounds like I still have a logic error but I send the workbook over to my Office 2010 system and the issue doesn't occur. Many thanks to those who offered suggestions and took a look.

 

[Norie]

I've only just seen these 2 posts.

Where in the code are you populating the Tasks combobox?

 

[dwooldridge]

Private Sub ComboTask_AfterUpdate()
Const rn As String = "ComboTask_AfterUpdate"
' call tracerA(rn, "")
Call ComboTaskLogic
Call ComboTextLeft(Me.ComboTask, rn)
' call tracerZ(rn, "")
End Sub

Private Sub ComboTaskLogic()
Const rn As String = "ComboTaskLogic"
Dim TskRow As Long
Dim str As String
' call tracerA(rn, "")
With Me
.cbFinishTask = False ' Reset this to false when we change tasks
If Me.ComboTask.ListIndex = -1 Then
Me.ComboTask.Clear ' This forces us to repopulate when we come back to the control
Else
' Tasks can have an over-ride on the bill status
Call ControlBillStatus
' If we have a task then we might have a default module
If .ComboMod.ListIndex = -1 Then ' No current module
TskRow = .ComboTask.List(.ComboTask.ListIndex, ccbTim_Tsk_Row)
str = shLis.Cells(TskRow, cLis_Tsk_PSI_Module_Id)
If Not NoKey(str) Then
' Module is populated at userform activate
.ComboMod.ListIndex = SearchComboBox(str, .ComboMod, ccbTim_Mod_PSI_Module_Id)
If .ComboMod.ListIndex > -1 Then
Call SetCbDisabled(.ComboMod, False)
Call AddMsgTxt("Loaded default module from task.")
End If
End If
End If
End If
End With

Call PopulateLbCheckOff
Call enableControls
' call tracerZ(rn, "")
End Sub

Private Sub ComboTask_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Const rn As String = "ComboTask_BeforeUpdate"
' call tracerA(rn, "")
' call tracerZ(rn, "")
End Sub

Private Sub ComboTask_Enter()
Const rn As String = "ComboTask_Enter"
' call tracerA(rn, "")
If Me.ComboTask.ListCount = 0 And Me.ComboPlan.ListIndex > -1 Then Call populateComboTask
' call tracerZ(rn, "")
End Sub

Private Sub ComboTask_Click()
Const rn As String = "ComboTask_Click"
' call tracerA(rn, "")
With Me
If .ComboTask.Tag = "Y" Then
Call ComboTaskLogic ' No BeforeUpdate/AfterUpdate so just execute
.ComboTask.Tag = ""
End If
End With
' call tracerZ(rn, "")
End Sub

 

[Norie]

Sorry but I don't se anything in that code to populate ComboTask.

Have you maybe missed out something, say the code for populateComboTask?

Anyway, have you tried calling populateComboTask from ComboPlan's change event?

 

[dwooldridge]

Private Sub populateComboTask()
    Const rn                                     As String = "populateComboTask"
    Dim strKey                                   As String
    Dim fRange                                   As Range
    Dim c1                                       As String
    Dim cel                                      As Range
    ' call tracerA(rn, "")
    With Me
        .ComboTask.Clear
        ' Tasks are linked to plans
        If Me.ComboPlan.ListIndex = -1 Then GoTo ce
        If shSto.Range(cStoTsk_LstRec) < cLis_FDR Then GoTo ce
        strKey = Me.ComboPlan.List(Me.ComboPlan.ListIndex, ccbTim_Pln_PSI_Plan_Id)
        c1 = makeAddressRange(cLis_Tsk_PSI_Plan_Id, cLis_FDR, cLis_Tsk_PSI_Plan_Id, shSto.Range(cStoTsk_LstRec))
        Set fRange = Find_Range(strKey, shLis.Range(c1), xl_Values, xl_whole, False)
        If Not fRange Is Nothing Then
            For Each cel In fRange
                .ComboTask.AddItem shLis.Cells(cel.row, cLis_Tsk_Rn_Descriptor)
                .ComboTask.List(.ComboTask.ListCount - 1, ccbTim_Tsk_PSI_Task_Id) = shLis.Cells(cel.row, cLis_Tsk_PSI_Task_Id)
                .ComboTask.List(.ComboTask.ListCount - 1, ccbTim_Tsk_Row) = cel.row
            Next cel
            If .ComboTask.ListCount = 1 Then
                .ComboTask.ListIndex = 0                               ' 1 task so default to it
                Call AddMsgTxt("There is only one task for this project so it has been automatically selected.")
            End If
        End If
    End With
ce:
    Me.cbFinishTask = False
    Call clearLbCheckOff
    ' call tracerZ(rn, "")
End Sub

I used to have what is currently in the CLICK event in the CHANGE event. Didn't notice any difference and I didn't like the code being there as it fired every time the user typed into the field. My tests on event firing indicated CLICK happened after CHANGE so it seemed like six of one / half dozen of the other.

 

[Norie]

Why is there so much code?

Is that just to try and find out what the problem is?

If it is, then it's overkill

Might even make it harder to find the problem.

 

[dwooldridge]

OK, interesting observation. There's always something to be learned - maybe my approach is wrong. Let's take a look. First the 'call tracer.....' statements are commented out so they don't count. They are the mechanism I used to trace execution of routines. Next:

.ComboTask.Clear

Seems reasonable to clear the combobox before populating it. The contents are based upon what the user selected in ComboPlan.

' Tasks are linked to plans

If there is no selection in ComboPlan then we bypass the populate as there is nothing to do.

If Me.ComboPlan.ListIndex = -1 Then GoTo ce

I check to make sure we have task records and if not I get out - no need to do a search for task records (there are 3 or 4 thousand)

If shSto.Range(cStoTsk_LstRec) < cLis_FDR Then GoTo ce

I get the Plan key. This is what links the combo boxes together

strKey = Me.ComboPlan.List(Me.ComboPlan.ListIndex, ccbTim_Pln_PSI_Plan_Id)

I build a range to search ("A1:A3400") for example

c1 = makeAddressRange(cLis_Tsk_PSI_Plan_Id, cLis_FDR, cLis_Tsk_PSI_Plan_Id, shSto.Range(cStoTsk_LstRec))

I build a list of qualifying task cells

Set fRange = Find_Range(strKey, shLis.Range(c1), xl_Values, xl_whole, False)

No qualifying cells - nothing to do

If Not fRange Is Nothing Then

I cycle through the range. This might be only 10 or 20 cells out of the 3 or 4 thousand

For Each cel In fRange

I populate ComboTask

.ComboTask.AddItem shLis.Cells(cel.row, cLis_Tsk_Rn_Descriptor)
.ComboTask.List(.ComboTask.ListCount - 1, ccbTim_Tsk_PSI_Task_Id) = shLis.Cells(cel.row, cLis_Tsk_PSI_Task_Id)
.ComboTask.List(.ComboTask.ListCount - 1, ccbTim_Tsk_Row) = cel.row
Next cel

If it turns out we had only 1 task to populate in ComboTask then we make that the default selection so the user doesn't have to select it. The AddMsgTxt call puts text into a running TextBox control so the user knows what we did.

If .ComboTask.ListCount = 1 Then
.ComboTask.ListIndex = 0 ' 1 task so default to it
Call AddMsgTxt("There is only one task for this project so it has been automatically selected.")
End If
End If
End With

All done

ce:

There are a couple of supporting controls that need to have values set so regardless of whether we actually populated ComboTask or not we do that now.

Me.cbFinishTask = False
Call clearLbCheckOff


It seems pretty tight to me. I guess I could pull out the last two statements but if memory serves I tried that and didn't get much difference. Let's remember what the symptom is: The user clicks the down arrow in the combobox. Items appear. He selects one and Excel ignores the selection. If he repeats then it is selected. Also the process works in Excel 2010.

 

[Norie]

Have you tried just setting up breakpoints, stepping through the code etc?

By the way, why do you have enumerations for your own versions of existing Excel VBA constant values, couldn't you use the built-in constants?