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.

 

[Andrew Poulsom]

The default event for a ComboBox is Change. Why don't you use that? If you want to suppress events see:

Suppressing Events In UserForms

 

[Norie]

If you populate a combobox on enter it could slow things down, which might confuse the user.

The combobox should be ready to use as soon as the user enters it.

Not quite sure I get your car analogy - generally you can't start a car unless you are in it.

 

[dwooldridge]

I appreciate your point on the delay.

Sure you can start your car remotely. If the manufacturer doesn't offer the option then I know you can purchase security system addons that give that ability. Not uncommon in Northern areas to start remotely and let the car warm up before jumping in!

 

[Norie]

You want to start the car before you get in it so that's it's ready to use as soon as you enter it.

Same for a combobox, populate it before it's entered so it's ready to use.

 

[dwooldridge]

Touche

 

[Norie]

Eh, not sure what you mean by that.s

 

[LEE355]

Try this on a userform with four comboboxes:

Private Sub ComboBox1_Change()
  With ComboBox1
    If .Value = "Chevy" Then
      With ComboBox2
        .Clear
        .AddItem "Corvette"
        .AddItem "Mabilbu"
        .AddItem "Monte Carlo"
      End With
    ElseIf .Value = "Ford" Then
      With ComboBox2
        .Clear
        .AddItem "Focus"
        .AddItem "Mustang"
        .AddItem "Taurus"
    End With
  End If
End With
End Sub
Private Sub ComboBox3_Change()
  With ComboBox3
    If .Value = "Interior" Then
        With ComboBox4
          .AddItem "Standard"
          .AddItem "Deluxe"
          .AddItem "DeluxeII"
        End With
        ElseIf .Value = "Exterior" Then
          With ComboBox4
            .Clear
            .AddItem "Black"
            .AddItem "Blue"
            .AddItem "White"
        End With
        ElseIf .Value = "Sun Roof" Then
          With ComboBox4
            .Clear
            .AddItem "Yes"
            .AddItem "No"
        End With
     End If
  End With
End Sub
Private Sub UserForm_Initialize()
  With ComboBox1
    .AddItem "Chevy"
    .AddItem "Ford"
 End With
  With ComboBox3
    .AddItem "Interior"
    .AddItem "Exterior"
    .AddItem "Sun Roof"
 End With
End Sub

 

[dwooldridge]

I mean good point - no other implications. Like Ken Watanabe said "We are having a good conversation".

Alas, Excel drives me nuts. I sent my workbook over to my desktop computer running Office 2010 and the bleeding thing runs fine.

 

[Norie]

No problem.

Any chance of look at the workbook?

 

[dwooldridge]

I've been working on that but I've gotten to the point where it will work, then fail, then work. I sent the failing workbook to my desktop machine running Office 2010 and it works fine. So back to my development Office 2007 machine and I notice that there are some service packs available. I'm loading them now to see what happens.